Re: How to start slave after pg_basebackup. Why min_wal_size and wal_keep_segments are duplicated

Hi Andrus,

> On 01. Jun, 2020, at 10:17, Andrus <kobruleht2(at)hot(dot)ee> 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