.::: Change Standby Slave Database to Master/Normal on PostgreSQL ERROR: cannot execute CREATE DATABASE in a read-only transaction :::.

A. Change Database Standby/Slave to Normal /Master

1. test create database for test write

teguhth=# create database tka;

ERROR:  cannot execute CREATE DATABASE in a read-only transaction

teguhth=# 

2. Promote Standby/Slave

/usr/pgsql-13/bin/pg_ctl promote -D /var/lib/pgsql/13/data

[root@teguhth02 ~]# su - postgres

Last login: Sun Aug  1 15:14:46 WIB 2021 on pts/0

-bash-4.2$ /usr/pgsql-13/bin/pg_ctl promote -D /var/lib/pgsql/13/data

waiting for server to promote.... done

server promoted

-bash-4.2$

3. test create database for test write again 

postgres=# create database tka;

postgres=# select datname from pg_database;

  datname  

-----------

 postgres

 teguhth

 template1

 template0

 tt

 tka

(6 rows)

postgres=#

3. Check Master and now using standalone 

[root@teguhth01 13]# su - postgres

Last login: Sun Aug  1 14:10:30 WIB 2021 on pts/1

-bash-4.2$ psql -x -c "select * from pg_stat_replication;";

(0 rows)

-bash-4.2$ 

B. Change database Normal / master to Standby/Slave

detail https://teguhth.blogspot.com/2021/08/how-to-setting-streaminng-replication.html

1. Move / backup directory data <stop postgresql service first>

-bash-4.2$ mv /var/lib/pgsql/13/data/ /var/lib/pgsql/13/data_old/

-bash-4.2$ ls

backup_label.old  pg_commit_ts   pg_notify     pg_subtrans  postgresql.auto.conf

backup_manifest   pg_dynshmem    pg_replslot   pg_tblspc    postgresql.conf

base              pg_hba.conf    pg_serial     pg_twophase  postmaster.opts

current_logfiles  pg_ident.conf  pg_snapshots  PG_VERSION   postmaster.pid

global            pg_logical     pg_stat       pg_wal

log               pg_multixact   pg_stat_tmp   pg_xact

-bash-4.2$

2. Replica master to standby 

-bash-4.2$ pg_basebackup -h 10.10.10.13 -U replicate -p 5432 -D /var/lib/pgsql/13/data/ -Fp -Xs -P -R

Password: 

41243/41243 kB (100%), 1/1 tablespace

-bash-4.2$ cd data

-bash-4.2$ pwd

/var/lib/pgsql/13/data

-bash-4.2$ ls

backup_label      pg_commit_ts   pg_notify     pg_subtrans  postgresql.auto.conf

backup_manifest   pg_dynshmem    pg_replslot   pg_tblspc    postgresql.conf

base              pg_hba.conf    pg_serial     pg_twophase  standby.signal

current_logfiles  pg_ident.conf  pg_snapshots  PG_VERSION

global            pg_logical     pg_stat       pg_wal

log               pg_multixact   pg_stat_tmp   pg_xact

-bash-4.2$ 

3. restart standby database 

[root@teguhth02 13]# systemctl restart postgresql-13

4. testing 

from slave/standby 

postgres=# create database xxx;

ERROR:  cannot execute CREATE DATABASE in a read-only transaction

from master / primary 

postgres=# create database xxx;

postgres=# postgres=# select datname from pg_database;

  datname  

-----------

 postgres

 teguhth

 template1

 template0

 tt

 tka

 xxx

(6 rows)

5. Check Master and now using standalone 

-bash-4.2$ psql -x -c "select * from pg_stat_replication;";

-[ RECORD 1 ]----+------------------------------

pid              | 2726

usesysid         | 16384

usename          | replicate

application_name | walreceiver

client_addr      | 10.10.10.14

client_hostname  | 

client_port      | 50654

backend_start    | 2021-08-01 15:35:24.007787+07

backend_xmin     | 

state            | streaming

sent_lsn         | 0/B000C10

write_lsn        | 0/B000C10

flush_lsn        | 0/B000C10

replay_lsn       | 0/B000C10

write_lag        | 

flush_lag        | 

replay_lag       | 

sync_priority    | 0

sync_state       | async

reply_time       | 2021-08-01 15:50:17.587653+07

-bash-4.2$