Hi!
How to create replication server ?
I always do it this way and it work for me:
$ pg_basebackup -h ${PGHOST} -p ${PGPORT} -U replicator -W -R -D ${PGDATA} -P -v -Fp -Xs
After that, I edit ${PGDATA}/postgresql.conf and (w/ PostgreSQL 11 and older ${PGDATA}/recovery.conf) to make it do what I want and
then I just launch it:
$ pg_ctl start
My script does the same thing as your comands.
From that moment onward, it replicates and applies to the replica. Checks in pg_stat_replication on the master and
pg_stat_wal_receiver on the replica >confirm that. They also show the WAL switches.
To provoke a WAL switch I always do:
postgres=# checkpoint; select pg_switch_wal();
CHECKPOINT
pg_switch_wal
I just don't understand what you're trying to achieve here.
I want to create replication server.
My guess is, you want to stop and backup the old database cluster,
Old cluster is empty, from initdb. Backup is not needed
then create a new one in its old directory, right?
pg_basebackup creates new main directory.
In this case, you probably need to change your script to something like this:
PGHOST=remote.example.com
PGPASSWORD=mypass
PGUSER=replikaator
PGDATA=/var/lib/postgresql/12/main
export PGHOST PGPASSWORD PGUSER PGDATA
/etc/init.d/postgresql stop
mv ${PGDATA} /var/lib/postgresql/12/mainennebaasbakuppi
pg_basebackup -h ${PGHOST} -p ${PGPORT} -U ${PGUSER} -W -R -D ${PGDATA} -P -v -Fp -Xs
/etc/init.d/postgresql start
pg_basebackup uses environment varuables if not specified in command line. So my script does the same thing.
Note that my invocation of pg_basebackup asks for the replicator password. This is intended. You'd probably want to change that.
Also, no need to play around with ownership and permissions. Do it as "postgres", not as "root".
I tried
sudo --user=postgres pg_basebackup ....
but got error
could not change directory to "/root": Permission denied
Andrus.
