3

I have configured master node the way you would configure it on 9.4, but I am getting this error on the slave node:

WARNING:  pg_stop_backup still waiting for all required WAL segments to be archived (120 seconds elapsed)
HINT:  Check that your archive_command is executing properly.  pg_stop_backup can be canceled safely, but the database backup will not be usable without all the WAL segments.

I did go in and enable archiving on the master node which was not something you had to do on 9.4 and I cannot seem to find a good guide on how to do master slave replication for 9.6.

I have not setup streaming replication. This is what I know how to do from working with PostgreSQL 9.4:

[root@li832-123 ~]# cd /var/lib/pgsql/9.6/data/

[root@li832-123 data]# vim postgresql.conf

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)

Now this part below in 9.4 you had the option of changing it to hot_standby but in 9.6 you do not get that option, the closest would be replica:

wal_level = replica                     # minimal, replica, or logical

This part below is the same as 9.4:

max_wal_senders = 1             # max number of walsender processes
                                # (change requires restart)
wal_keep_segments = 100         # in logfile segments, 16MB each; 0 disables

same here:

synchronous_standby_names = 'postgres_clone'    # standby servers that provide sync rep

On the Slave server, I stopped Postgres:

systemctl stop postgresql-9.6

Then:

su - postgres

listed the databases on the slave to verify that there was nothing there from the master server.

cd /var/lib/pgsql/9.6/data

then:

rm -rf *

to remove everything in the data directory on the slave node so the primary configuration can be pulled forward. Then I went back in as the postgres user and performed the following command:

pg_basebackup -D /var/lib/pgsql/9.6/data -h <ip-of-master-node> -U replica

so I almost got it, until on the slave server I tried to

systemctl start posgresql

and I got this error:

The result is failed.
Jan 04 03:39:06 li1033-198.members.linode.com systemd[1]: Unit postgresql.service entered failed state.
Jan 04 03:39:06 li1033-198.members.linode.com systemd[1]: postgresql.service failed.
Jan 04 03:39:06 li1033-198.members.linode.com pg_ctl[5144]: pg_ctl: could not start server
Jan 04 03:39:06 li1033-198.members.linode.com pg_ctl[5144]: Examine the log output.
Jan 04 03:39:06 li1033-198.members.linode.com polkitd[3207]: Unregistered Authentication Agent for unix-process:5134:368663 (system bus name :1.43, object pat
Jan 04 03:39:11 li1033-198.members.linode.com sshd[5151]: Invalid user admin from 49.231.153.151
Jan 04 03:39:11 li1033-198.members.linode.com sshd[5151]: input_userauth_request: invalid user admin [preauth]
Jan 04 03:39:11 li1033-198.members.linode.com sshd[5151]: pam_unix(sshd:auth): check pass; user unknown
Jan 04 03:39:11 li1033-198.members.linode.com sshd[5151]: pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=49.231.153.1
Jan 04 03:39:13 li1033-198.members.linode.com sshd[5151]: Failed password for invalid user admin from 49.231.153.151 port 41322 ssh2
Jan 04 03:39:13 li1033-198.members.linode.com sshd[5151]: pam_unix(sshd:auth): check pass; user unknown
Jan 04 03:39:15 li1033-198.members.linode.com sshd[5151]: Failed password for invalid user admin from 49.231.153.151 port 41322 ssh2
Jan 04 03:39:16 li1033-198.members.linode.com sshd[5151]: pam_unix(sshd:auth): check pass; user unknown
  • 1
    The steps seem right for setting up streaming replication, which I guess is what you had in 9.4 as well. Empty the db folder in the replica, copy or pg_basebackup from the master, ... and then? What did you after that? Or you got this error during the pg_basebackup? – ypercubeᵀᴹ Dec 30 '16 at 16:33
  • Correct, I got the error during the pg_basebackup. – Daniel Dec 30 '16 at 16:34
  • 1
    Then something needs fixing in the master postgresql.conf. What are the archive_mode and archive_command settings in master? – ypercubeᵀᴹ Dec 30 '16 at 16:35
  • Oh you have synchronous_standby_names. Which means synchronous replication (not asynchronous). I haven't set up this variation before so I'll let others to try figure what is wrong. – ypercubeᵀᴹ Dec 30 '16 at 16:40
  • But the Postgrs docs about synchronous replication say: "Once streaming replication has been configured, configuring synchronous replication requires only one additional configuration step: synchronous_standby_names must be set to a non-empty value." So, I suggest you comment that setting away and start again without it until you have setup (asynchronous) streaming replication up and running. And then change the setting – ypercubeᵀᴹ Dec 30 '16 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.