Oracle 26ai Grid, database installation on Linux 86_64 Step-by-Step

 

Click here --> Oracle 26ai Grid, database installation on Linux 86_64 Step-by-Step

Click here --> Oracle 26ai Grid, database installation on Linux 86_64 Step-by-Step

 

PostgreSQL 16: Streaming Replication

Assumptions:

−      Same version of PostgreSQL is installed on the Primary and Standby DB servers.

−      PostgreSQL 16 DB cluster is already up and running on both servers.

Primary database:

Prepare the PostgreSQL 16 primary database to ship the WAL files to the standby database using the below steps:

−      Updating the postgresql.conf file.

−      Updating the pg_hba.conf.

−      Creating replication role and reload configuration.

postgresql.conf:

1.  Take a safe backup of postgresql.conf file.

[root@prd-prime data]# hostname

prd-prime

[root@prd-prime data]# pwd

/u02/pgsql/16/data

cp  postgresql.conf  postgresql.conf_2_1_2025

2.  Add the below parameters into it:

archive_mode = on

synchronous_commit = on

#hot_standby = on

wal_level = replica

wal_log_hints = on

wal_keep_segments = 300

wal_keep_size = 1024

max_wal_senders = 10

PG_HBA.conf:

1.   Take a safe backup of pg_hba.conf file.

cp  pg_hba.conf  pg_hba.conf_2_1_2025

2.   Add the below parameters into it:

host    replication     rep_user        xxx.xx.xx.xx/32         md5

host    replication     rep_user        yyy.yy.yy.yy/32         md5

Note 1: rep_user will be created shortly.

Note 2: IP Address of Primary and Standby, xxx.xx.xx.xx and yyy.yy.yy.yy

CREATE REPLICATION ROLE & Reload configuration file:

1.  Create replication role:

postgres=# CREATE ROLE rep_user WITH REPLICATION LOGIN PASSWORD 'Welcome2025';

CREATE ROLE

2.  Run reload configuration to avoid DB restart.

postgres=# SELECT pg_reload_conf();

pg_reload_conf

----------------

t

Standby database:

Prepare the PostgreSQL 16 standby database to receive WAL files from the primary database using the below steps:

−      Creating a new data directory and grant ownership & privileges.

−      Run pg_basebackup with required parameters.

−      Update postgresql.service file.

−      View the auto-created postgresql.auto.conf. (Read only).

−      Update the standby.signal file.

−      Update the postgresql.conf.

−      Start the cluster.

Create data directory and grant access to postgres user:

1.        Create a new data directory and grant required privileges and ownership:

[root@prd-stand ]# hostname

prd-stand

[root@prd-stand ]# cd /u02/pgsql/16/

[root@prd-stand ]# mkdir datarep

[root@prd-stand ]# sudo chown -R postgres:postgres /u02/pgsql/16/datarep

[root@prd-stand ]# sudo chmod 750 /u02/pgsql/16/datarep

run pg_basebackup:

2.  Switch to Postgres user and run base backup with correct path and parameters:

[postgres@prd-stand] tmux new -s mysession

[postgres@prd-stand bin] cd /usr/bin

[postgres@prd-stand bin]$ pg_basebackup -h  xxx.xx.xx.xx -p 5432 -U rep_user -D /u02/pgsql/16/datarep -Fp -Xs -P -R --wal-method=stream

Password:

1025169417/1800888245 kB (56%), 0/1 tablespace

3.   Once the base backup is completed, ensure all the files are copied under datarep directory:

[root@prd-stand datarep]# ls -ltr

total 64444

-rw-------. 1 postgres postgres      231 Feb  1 12:54 backup_label

drwx------. 6 postgres postgres       66 Feb  1 12:54 base

drwx------. 3 postgres postgres   131072 Feb  2 03:57 pg_wal

drwx------. 2 postgres postgres     4096 Feb  2 03:57 log

drwx------. 2 postgres postgres       10 Feb  2 03:57 pg_commit_ts

drwx------. 4 postgres postgres       84 Feb  2 03:57 pg_logical

drwx------. 2 postgres postgres       10 Feb  2 03:57 pg_dynshmem

drwx------. 4 postgres postgres       48 Feb  2 03:57 pg_multixact

drwx------. 2 postgres postgres       10 Feb  2 03:57 pg_twophase

drwx------. 2 postgres postgres       10 Feb  2 03:57 pg_tblspc

drwx------. 2 postgres postgres       10 Feb  2 03:57 pg_subtrans

drwx------. 2 postgres postgres       10 Feb  2 03:57 pg_stat_tmp

drwx------. 2 postgres postgres       10 Feb  2 03:57 pg_stat

drwx------. 2 postgres postgres       10 Feb  2 03:57 pg_snapshots

drwx------. 2 postgres postgres       10 Feb  2 03:57 pg_serial

drwx------. 2 postgres postgres       10 Feb  2 03:57 pg_replslot

drwx------. 2 postgres postgres       10 Feb  2 03:57 pg_notify

-rw-------. 1 postgres postgres    29693 Feb  2 03:57 postgresql.conf.bak

-rw-------. 1 postgres postgres      425 Feb  2 03:57 postgresql.auto.conf

drwx------. 2 postgres postgres     4096 Feb  2 03:57 pg_xact

-rw-------. 1 postgres postgres        3 Feb  2 03:57 PG_VERSION

-rw-------. 1 postgres postgres     2640 Feb  2 03:57 pg_ident.conf

-rwxrwxrwx. 1 postgres postgres     5499 Feb  2 03:57 pg_hba.conf_original

-rw-------. 1 postgres postgres      343 Feb  2 03:57 nohup.out

-rw-------. 1 postgres postgres    30718 Feb  2 03:57 postgresql.conf_1_28_2025

-rw-------. 1 postgres postgres     5829 Feb  2 03:57 pg_hba.conf_works_exceptpostgresuser

-rw-------. 1 postgres postgres     5824 Feb  2 03:57 pg_hba.conf_gui_cl_gui_works

-rw-------. 1 postgres postgres       30 Feb  2 03:57 current_logfiles

-rw-------. 1 postgres postgres    30721 Feb  2 03:57 postgresql.conf_2_1_2025

-rw-------. 1 postgres postgres     5935 Feb  2 03:57 pg_hba.conf_2_1_2025

-rw-------. 1 postgres postgres     6052 Feb  2 03:57 pg_hba.conf

drwx------. 2 postgres postgres     4096 Feb  2 03:57 global

-rw-------. 1 postgres postgres 65586211 Feb  2 03:57 backup_manifest

-rw-------. 1 postgres postgres      302 Feb  2 10:53 standby.signal

-rw-------. 1 postgres postgres    31003 Feb  2 10:57 postgresql.conf

postgresql.service:

4. Update the postgresql.service file with new data directory for Environment (PGDATA):

[root@prd-stand datarep]# vim /lib/systemd/system/postgresql.service

Environment=PGDATA=/u02/pgsql/16/datarep

postgresql.auto.conf (Read only):

5.   Check the auto-created postgresql.auto.conf file for primany conninfo value:

bash-4.4$ cat postgresql.auto.conf

# Do not edit this file manually!

# It will be overwritten by the ALTER SYSTEM command.

max_locks_per_transaction = '1024'

primary_conninfo = 'user=rep_user password=Welcome2025 channel_binding=prefer host= xxx.xx.xx.xx port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'

bash-4.4$ ls -ltr postgresql.auto.conf

standby.signal:

6.  Check the auto-created EMPTY standby.signal file and add primary_conninfo from step 5:

[root@prd-stand datarep]# su postgres

bash-4.4$ cat /u02/pgsql/16/datarep/standby.signal

primary_conninfo = 'user=rep_user password=Welcome2025 channel_binding=prefer host= xxx.xx.xx.xx port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'

bash-4.4$

postgresql.config:

7.  Update the postgresql.conf:

[root@prd-stand datarep]# su postgres

bash-4.4$ vi /u02/pgsql/16/datarep/postgresql.conf

data_directory = '/u02/pgsql/16/datarep'

hot_standby = on

#shared_preload_libraries = 'timescaledb,pg_cron'

primary_conninfo = 'user=rep_user password=Welcome2025 channel_binding=prefer host= xxx.xx.xx.xx port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable’

Start the cluster:

8.  Start the cluster using new data directory:

[root@prd-stand datarep]# systemctl start postgresql.service

[root@prd-stand datarep]# systemctl status postgresql.service

● postgresql.service - PostgreSQL database server

Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)

Active: active (running) since Sun 2025-02-02 11:19:37 EST; 19s ago

Process: 780094 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=0/SUCCESS)

Main PID: 780097 (postgres)

Tasks: 6 (limit: 1234086)

Memory: 13.0G

CGroup: /system.slice/postgresql.service

├─780097 /usr/bin/postgres -D /u02/pgsql/16/datarep

├─780099 postgres: logger

├─780100 postgres: checkpointer

├─780101 postgres: background writer

├─780102 postgres: startup recovering 0000000100000DC6000000EE

└─781295 postgres: walreceiver streaming DC6/EEEC0000

Check the log file:

[root@prd-stand log]# su postgres

bash-4.4$ pwd

/u02/pgsql/16/datarep/log

bash-4.4$ id

uid=26(postgres) gid=26(postgres) groups=26(postgres) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

bash-4.4$ ls -ltr

total 35588

-rw-------. 1 postgres postgres 6002122 Feb  2 03:57 postgresql-Tue.log

-rw-------. 1 postgres postgres 6251455 Feb  2 03:57 postgresql-Wed.log

-rw-------. 1 postgres postgres 6074026 Feb  2 03:57 postgresql-Thu.log

-rw-------. 1 postgres postgres 5720180 Feb  2 03:57 postgresql-Fri.log

-rw-------. 1 postgres postgres 5694620 Feb  2 03:57 postgresql-Sat.log

-rw-------. 1 postgres postgres 5702075 Feb  2 03:57 postgresql-Mon.log

-rw-------. 1 postgres postgres  938135 Feb  2 15:37 postgresql-Sun.log

bash-4.4$ tail -20f postgresql-Sun.log

2025-02-02 15:17:25.074 EST [780100] LOG:  restartpoint complete: wrote 42916 buffers (2.7%); 0 WAL file(s) added, 0 removed, 11 recycled; write=269.716 s, sync=0.033 s, total=269.768 s; sync files=253, longest=0.005 s, average=0.001 s; distance=465555 kB, estimate=492192 kB; lsn=DD0/CC59B128, redo lsn=DD0/C0170158

2025-02-02 15:17:25.074 EST [780100] LOG:  recovery restart point at DD0/C0170158

2025-02-02 15:17:25.074 EST [780100] DETAIL:  Last completed transaction was at log time 2025-02-02 15:17:24.862653-05.

2025-02-02 15:17:55.105 EST [780100] LOG:  restartpoint starting: time

2025-02-02 15:22:29.742 EST [780100] LOG:  restartpoint complete: wrote 28778 buffers (1.8%); 0 WAL file(s) added, 36 removed, 0 recycled; write=269.955 s, sync=4.568 s, total=274.638 s; sync files=277, longest=4.184 s, average=0.017 s; distance=216945 kB, estimate=464667 kB; lsn=DD0/E1E315C8, redo lsn=DD0/CD54C7F8

2025-02-02 15:22:29.743 EST [780100] LOG:  recovery restart point at DD0/CD54C7F8

2025-02-02 15:22:29.743 EST [780100] DETAIL:  Last completed transaction was at log time 2025-02-02 15:22:29.362784-05.

2025-02-02 15:22:55.768 EST [780100] LOG:  restartpoint starting: time

2025-02-02 15:27:25.303 EST [780100] LOG:  restartpoint complete: wrote 90546 buffers (5.8%); 1 WAL file(s) added, 21 removed, 0 recycled; write=269.309 s, sync=0.009 s, total=269.536 s; sync files=210, longest=0.003 s, average=0.001 s; distance=347360 kB, estimate=452937 kB; lsn=DD0/EBB84098, redo lsn=DD0/E2884AE0

2025-02-02 15:27:25.304 EST [780100] LOG:  recovery restart point at DD0/E2884AE0

2025-02-02 15:27:25.304 EST [780100] DETAIL:  Last completed transaction was at log time 2025-02-02 15:27:24.979757-05.

2025-02-02 15:27:55.309 EST [780100] LOG:  restartpoint starting: time

2025-02-02 15:32:25.149 EST [780100] LOG:  restartpoint complete: wrote 34176 buffers (2.2%); 0 WAL file(s) added, 10 removed, 0 recycled; write=269.748 s, sync=0.039 s, total=269.840 s; sync files=222, longest=0.003 s, average=0.001 s; distance=169634 kB, estimate=424606 kB; lsn=DD1/1E80F358, redo lsn=DD0/ECE2D658

2025-02-02 15:32:25.149 EST [780100] LOG:  recovery restart point at DD0/ECE2D658

2025-02-02 15:32:25.149 EST [780100] DETAIL:  Last completed transaction was at log time 2025-02-02 15:32:24.124176-05.

2025-02-02 15:32:55.159 EST [780100] LOG:  restartpoint starting: time

2025-02-02 15:37:25.119 EST [780100] LOG:  restartpoint complete: wrote 37302 buffers (2.4%); 0 WAL file(s) added, 0 removed, 32 recycled; write=269.866 s, sync=0.052 s, total=269.960 s; sync files=256, longest=0.011 s, average=0.001 s; distance=838543 kB, estimate=838543 kB; lsn=DD1/2D5D28D8, redo lsn=DD1/201115E8

2025-02-02 15:37:25.119 EST [780100] LOG:  recovery restart point at DD1/201115E8

2025-02-02 15:37:25.119 EST [780100] DETAIL:  Last completed transaction was at log time 2025-02-02 15:37:23.864674-05.

2025-02-02 15:37:55.150 EST [780100] LOG:  restartpoint starting: time

Validate the replication & check the replication lag:

@Primary:

postgres=# select pg_is_in_recovery();

pg_is_in_recovery

-------------------

f

(1 row)

postgres=# SELECT * FROM pg_stat_replication;

(1 row)

@Standby:

postgres=# select pg_is_in_recovery();

pg_is_in_recovery

-------------------

t

(1 row)

postgres=# SELECT * FROM pg_stat_wal_receiver;

@Find Replication Lag from Standby:

postgres=# SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS lag_bytes;

Thanks!

Govind.