Year: 2017

Install PostgreSQL 9.6 on RHEL 7

Use “yum install” to install PostgreSQL.

1)  Go to https://www.postgresql.org/download, and choose your platform accordingly.

PostgreSQL Download URL Pre-built Binary Package
Pre-built Binary Package

2)  Action on the below steps as per instructions :

PostgreSQL YUM Repository
PostgreSQL YUM Repository
  • Install the repository RPM:

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm

  • Install the client packages:

yum install postgresql96

  • Optionally install the server packages:

yum install postgresql96-server

  • Optionally initialize the database and enable automatic start:

/usr/pgsql-9.6/bin/postgresql96-setup initdb
systemctl enable postgresql-9.6
systemctl start postgresql-9.6

[root@racnode1 ~]# cd /tmp
[root@racnode1 tmp]# yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
Loaded plugins: ulninfo
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
pgdg-redhat96-9.6-3.noarch.rpm | 4.7 kB 00:00:00
Examining /var/tmp/yum-root-p5F8y1/pgdg-redhat96-9.6-3.noarch.rpm: pgdg-redhat96-9.6-3.noarch
Marking /var/tmp/yum-root-p5F8y1/pgdg-redhat96-9.6-3.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package pgdg-redhat96.noarch 0:9.6-3 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================================
 Package Arch Version Repository Size
=============================================================================================================================================================================================
Installing:
 pgdg-redhat96 noarch 9.6-3 /pgdg-redhat96-9.6-3.noarch 2.7 k

Transaction Summary
=============================================================================================================================================================================================
Install 1 Package

Total size: 2.7 k
Installed size: 2.7 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
 Installing : pgdg-redhat96-9.6-3.noarch 1/1
 Verifying : pgdg-redhat96-9.6-3.noarch 1/1

Installed:
 pgdg-redhat96.noarch 0:9.6-3

Complete!
[root@racnode1 tmp]# yum install postgresql96
Loaded plugins: ulninfo
ksplice-uptrack | 951 B 00:00:00
ol7_UEKR3 | 1.2 kB 00:00:00
ol7_addons | 1.2 kB 00:00:00
ol7_latest | 1.4 kB 00:00:00
pgdg96 | 4.1 kB 00:00:00
(1/2): pgdg96/7Server/x86_64/group_gz | 249 B 00:00:01
(2/2): pgdg96/7Server/x86_64/primary_db | 151 kB 00:00:02
(1/7): ol7_addons/x86_64/primary | 73 kB 00:00:00
(2/7): ol7_addons/x86_64/updateinfo | 38 kB 00:00:00
(3/7): ksplice-uptrack/7Server/x86_64/primary | 2.0 kB 00:00:00
(4/7): ol7_UEKR3/x86_64/updateinfo | 79 kB 00:00:00
(5/7): ol7_latest/x86_64/updateinfo | 1.3 MB 00:00:01
(6/7): ol7_UEKR3/x86_64/primary | 27 MB 00:00:20
(7/7): ol7_latest/x86_64/primary | 26 MB 00:00:20
ksplice-uptrack 7/7
ol7_UEKR3 642/642
ol7_addons 238/238
ol7_latest 19326/19326
Resolving Dependencies
--> Running transaction check
---> Package postgresql96.x86_64 0:9.6.3-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql96-libs(x86-64) = 9.6.3-1PGDG.rhel7 for package: postgresql96-9.6.3-1PGDG.rhel7.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql96-9.6.3-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package postgresql96-libs.x86_64 0:9.6.3-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================================
 Package Arch Version Repository Size
=============================================================================================================================================================================================
Installing:
 postgresql96 x86_64 9.6.3-1PGDG.rhel7 pgdg96 1.3 M
Installing for dependencies:
 postgresql96-libs x86_64 9.6.3-1PGDG.rhel7 pgdg96 310 k

Transaction Summary
=============================================================================================================================================================================================
Install 1 Package (+1 Dependent package)

Total download size: 1.6 M
Installed size: 8.0 M

Is this ok [y/d/N]: y
Downloading packages:
(1/2): postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64.rpm | 310 kB 00:00:03
(2/2): postgresql96-9.6.3-1PGDG.rhel7.x86_64.rpm | 1.3 MB 00:00:04
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 415 kB/s | 1.6 MB 00:00:04
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
 Installing : postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64 1/2
 Installing : postgresql96-9.6.3-1PGDG.rhel7.x86_64 2/2
 Verifying : postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64 1/2
 Verifying : postgresql96-9.6.3-1PGDG.rhel7.x86_64 2/2

Installed:
 postgresql96.x86_64 0:9.6.3-1PGDG.rhel7

Dependency Installed:
 postgresql96-libs.x86_64 0:9.6.3-1PGDG.rhel7

Complete!
[root@racnode1 tmp]#

[root@racnode1 tmp]# yum install postgresql96-server
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package postgresql96-server.x86_64 0:9.6.3-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================================
 Package Arch Version Repository Size
=============================================================================================================================================================================================
Installing:
 postgresql96-server x86_64 9.6.3-1PGDG.rhel7 pgdg96 4.3 M

Transaction Summary
=============================================================================================================================================================================================
Install 1 Package

Total download size: 4.3 M
Installed size: 18 M
Is this ok [y/d/N]: y
Downloading packages:
postgresql96-server-9.6.3-1PGDG.rhel7.x86_64.rpm | 4.3 MB 00:00:05
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
 Installing : postgresql96-server-9.6.3-1PGDG.rhel7.x86_64 1/1
 Verifying : postgresql96-server-9.6.3-1PGDG.rhel7.x86_64 1/1

Installed:
 postgresql96-server.x86_64 0:9.6.3-1PGDG.rhel7

Complete!
[root@racnode1 tmp]#

[root@racnode1 tmp]# /usr/pgsql-9.6/bin/postgresql96-setup initdb
Initializing database ... OK

[root@racnode1 tmp]# systemctl enable postgresql-9.6
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-9.6.service to /usr/lib/systemd/system/postgresql-9.6.service.
--
-- Start PosgreSQL Server
--
[root@racnode1 tmp]# systemctl start postgresql-9.6
[root@racnode1 tmp]# ps -eaf | grep post
postfix 933 1885 0 15:40 ? 00:00:00 pickup -l -t unix -u
postgres 1259 1 0 15:41 ? 00:00:00 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
postgres 1262 1259 0 15:41 ? 00:00:00 postgres: logger process
postgres 1264 1259 0 15:41 ? 00:00:00 postgres: checkpointer process
postgres 1265 1259 0 15:41 ? 00:00:00 postgres: writer process
postgres 1266 1259 0 15:41 ? 00:00:00 postgres: wal writer process
postgres 1267 1259 0 15:41 ? 00:00:00 postgres: autovacuum launcher process
postgres 1268 1259 0 15:41 ? 00:00:00 postgres: stats collector process
root 1277 5694 0 15:41 pts/0 00:00:00 grep --color=auto post
root 1885 1 0 12:50 ? 00:00:00 /usr/libexec/postfix/master -w
postfix 1893 1885 0 12:50 ? 00:00:00 qmgr -l -t unix -u
[root@racnode1 tmp]#

[root@racnode1 ~]# su - postgres
Last login: Thu Jul 13 16:06:15 AEST 2017 on pts/0
-bash-4.2$ psql
psql (9.6.3)
Type "help" for help.

postgres=# \l
 List of databases
 Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
 | | | | | postgres=CTc/postgres
 template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
 | | | | | postgres=CTc/postgres
(3 rows)

postgres=#

ORA-65066: The specified changes must apply to all containers

For changing common user password, it should be done in CDB root container ( CDB$ROOT ).

$ sqlplus sys@pdb1 as sysdba
SQL> alter user system identified by “PasswordNew”;
alter user system identified by “PasswordNew”
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers

SQL> show con_name

CON_NAME
————
PDB1

SQL> connect / as sysdba
Connected.
SQL> alter user system identified by “PasswordNew”;

User altered.

SQL> show con_name

CON_NAME
————
CDB$ROOT

ORA-02065: illegal option for ALTER SYSTEM

For 10.2.0.5, set up parameter value by :
alter system set sga_target= scope= sid=;

In 10g database, when trying to change SGA_TARGET parameter in spfile, “ORA-02065: illegal option for ALTER SYSTEM” occurred.

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jun 13 15:56:37 2017
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter sga_target

NAME_COL_PLUS_SHOW_PARAM TYPE        VALUE_COL_PLUS_SHOW_PARAM
------------------------ ----------- --------------------------
sga_target               big integer 4G

SQL> alter system set sga_target=2g sid='RACTEST1' scope=spfile;
 alter system set sga_target=2g sid='RACTEST1' scope=spfile
 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

By swapping the order of “sid” and scope, it is working fine:

SQL> alter system set sga_target=2g scope=spfile sid='RACTEST1';

System altered.

SQL>

ORA-02158: invalid CREATE INDEX option

It seems Oracle Advanced Compression Feature does not apply to IOT tables.

Tried to apply Oracle Advanced Compression Feature onto IOT tables in 12.1.0.2, then got this error.

SQL> CREATE TABLE TEST_IOT_TBL
          ( id   number,
            sex  char(1),
           name  varchar2(20),
           CONSTRAINT TEST_IOT_TBL_PK PRIMARY KEY (id,sex ) ENABLE
         ) ORGANIZATION INDEX ;

Table created.

SQL> insert into TEST_IOT_TBL values ( 1,'M','test1');

1 row created.

SQL> insert into TEST_IOT_TBL values (2,'M','test2');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table TEST_IOT_TBL move compress for all operations;
 alter table TEST_IOT_TBL move compress for all operations
 *
ERROR at line 1:
ORA-02158: invalid CREATE INDEX option