RE: Streaming Replication Error

[<soumik.bhattacharjee@xxxxxxx>]

Thanks Jeff for your valuable inputs.

 

The setup-

 

MASTER

1.      I did the below –

psql -c "ALTER SYSTEM SET listen_addresses TO '*'";

2.      Created the user needed for replication

3.      Updated the pg_hba.conf

            echo "host replication replicator IP Address/32 md5" >> /etc/postgresql/12/main/pg_hba.conf

psql -c "select pg_reload_conf()"

 

SLAVE

1.      Stopped the postgres on slave

2.      rm -rf /var/lib/postgresql/12/main/*

3.      I ran the below one in Slave once all needed setups were done

                  pg_basebackup -h IP Address -U replicator -p 5432 -D $PGDATA -Fp -Xs -P -R

4.      Started the PostgreSQL using pg_ctl on the Slave.

 

-          Taken backup using pg_dump from a different instance.

-          Did a pg_restore on MASTER (The existing instance - MASTER)

-          The SLAVE  has all the data after the restore was done in MASTER – both were in sync.

-          But after that any new objects created on MASTER was not reflected on SLAVE the errors started coming in.

 

 

Please let me know if I did something wrong above step highlighted ? Does that mean I cannot refresh the MASTER anytime which should replicate to SLAVE?

 

Thanks

 

 

From: Jeff Janes <jeff.janes@xxxxxxxxx>
Sent: vrijdag 20 december 2019 17:45
To: Bhattacharjee, Soumik <soumik.bhattacharjee@xxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxxxxxxxx; pgsql-admin <pgsql-admin@xxxxxxxxxxxxxx>
Subject: Re: Streaming Replication Error

 

On Fri, Dec 20, 2019 at 11:08 AM <soumik.bhattacharjee@xxxxxxx> wrote:

Hi Experts,

 

I have set up streaming replication on PostgreSQL 12.1

 

The master and slave are configured as below and WAL files are accumulating on the master.

 

However, something is wrong as I get complaints that the WAL files are missing after a pg_restore on MASTER

 

You ran pg_restore on the master?  Why did you do that?  Doesn't that mean you now have a new master, different from the old master?  Did you restore into a new instance, or into the existing instance?  What command line did you use?

 

 

SLAVE

 ...

2019-12-20 16:37:08.042 CET [21981] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000000100000076 has already been removed

 

 

Then the pg_basebackup is run and the slave started.

 

Presumably you just didn't restart the slave.  You blew away the old one entirely, and started the new one created by the pg_basebackup.

 

What was the command line used for pg_basebackup?

 

 

The slave has all the data as of the time of the backup, but no new data from the WAL files, and the error above.

 

This is confusing.  You got the above errors before you did the new pg_basebackup, or after?

 

 

max_wal_senders = 10         

wal_keep_segments = 120

 

What have I mis-configured?

 

Your email doesn't seem to be written in chronological order, and you didn't include the parameters for the commands you ran, so it is hard to say what you did wrong, as we don't know what you did.

 

It could be that your replica is seeking files from the wrong master.

 

Do we need to enabled archive_mode = on for streaming replication?

 

That can sometimes be useful, but it is not necessary.  You can use a replication slot to force the master to retain sufficient logs, or you can set wal_keep_segments high enough that it (probably) keeps enough on its own.

 

One reason I sometimes find archive_mode to be useful in a streaming setup is that I can inject a compression step.  WAL files compress very well, and you don't get that compression with a streaming connection.  So if you fall behind and have a slow network, you can catch up much faster using a compressed archive.

 

Cheers,

 

Jeff