Hi Andrus,
> On 01. Jun, 2020, at 10:17, Andrus <[email protected]> wrote:
> Shell script starts server after pg_basebackup completes automatically:
>
> PGHOST=example.com
> PGPASSWORD=mypass
> PGUSER=replikaator
> export PGHOST PGPASSWORD PGUSER
> /etc/init.d/postgresql stop
> mv /var/lib/postgresql/12/main /var/lib/postgresql/12/mainennebaasbakuppi
> pg_basebackup --verbose --progress --write-recovery-conf -D
> /var/lib/postgresql/12/main
> chmod --recursive --verbose 0700 /var/lib/postgresql/12/main
> chown -Rv postgres:postgres /var/lib/postgresql/12/main
> /etc/init.d/postgresql start
>
> 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
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
---------------
C/50000128
(1 row)
I just don't understand what you're trying to achieve here. My guess is, you
want to stop and backup the old database cluster, then create a new one in its
old directory, right? 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
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".
Cheers,
Paul
