Comments on: Understanding Replication in PostgreSQL – How to Set Up PostgreSQL Streaming Replication https://www.percona.com/blog/setting-up-streaming-replication-postgresql/ Mon, 29 Apr 2024 16:18:09 +0000 hourly 1 https://wordpress.org/?v=6.5.2 By: zifnab https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10972276 Thu, 28 May 2020 10:58:37 +0000 https://www.percona.com/blog/?p=52469#comment-10972276 Hi Balasubramanian M P,
i guess $PGDATA is empty. You can check it with: echo $PGDATA.
Your screenshot shows you did the command with user root. Try user postgres.

]]> By: Push https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10971846 Thu, 20 Feb 2020 09:16:52 +0000 https://www.percona.com/blog/?p=52469#comment-10971846 Does it fail over in case master goes down ?

]]> By: avivallarapu https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10971684 Wed, 15 Jan 2020 18:44:56 +0000 https://www.percona.com/blog/?p=52469#comment-10971684 Hi Tony, Great to hear that the blog post helped you. You don’t have to remove those WAL segments from the pg_xlog of the Master.

]]> By: avivallarapu https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10971683 Wed, 15 Jan 2020 18:37:42 +0000 https://www.percona.com/blog/?p=52469#comment-10971683 You can definitely use an existing user as well. The user just needs to have the REPLICATION ROLE. Sometime we see user using postgres, the superuser to setup replication. It works, but not recommended as you do not really a superuser that has privileges to perform any possible action on the database, just to setup replication.

]]> By: Tony Libbrecht https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10971682 Wed, 15 Jan 2020 12:56:59 +0000 https://www.percona.com/blog/?p=52469#comment-10971682 Hi
A number of months ago, I have been setting up streaming replication between 2 locations.
Following your blog, setup went well and streaming worked well all the time. (Postgre 9.5).

Now an issue happened on master, and streaming stopped.
Therefore I want to take base backup on slave again and reinitiate streaming from scratch.
It is not important that I might lose some data. Just want to start over.

Question : on master, currently there are a number of wal files remaining in pg_xlog folder.
Do I need to remove the pg_xlog files on master, before taking pg_basebackup on slave ?

Thank you

]]> By: Shubha https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10971536 Mon, 16 Dec 2019 22:08:54 +0000 https://www.percona.com/blog/?p=52469#comment-10971536 Hi Avinash, This is an excellent write up!
I have a question on creating a new user for replication. Why cant we use an existing user for replication job as well?
Every blog I have come across suggests creating a new user for Replication.
I would really appreciate your insight on this.

Thanks!

]]> By: geno https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10971490 Mon, 25 Nov 2019 19:10:51 +0000 https://www.percona.com/blog/?p=52469#comment-10971490 “listen_addresses : Set it to * or the range of IP Addresses that need to be whitelisted to connect to your master PostgreSQL server. Your slave IP should be whitelisted too, else, the slave cannot connect to the master to replicate/replay WALs.”

1.
I dont think the word “whitelisted” is appropriate here. It implies something will be blocking the IP address unless specified.
Instead, your telling postgres which addresses to listen on for incoming connections.

2.
You are definately NOT adding the slave’s IP address in the ‘listen_addresses’ list on the master.
Go read the documentation again.

Otherwise, thanks for the article

]]> By: avivallarapu https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10971348 Wed, 30 Oct 2019 20:44:00 +0000 https://www.percona.com/blog/?p=52469#comment-10971348 Should not be much different but i can surely think of a post on it Kavita.

]]> By: avivallarapu https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10971347 Wed, 30 Oct 2019 20:43:22 +0000 https://www.percona.com/blog/?p=52469#comment-10971347 You see any errors in the PG logs of both Master and Standby ?

]]> By: Balasubramanian M P https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10971298 Fri, 18 Oct 2019 14:18:26 +0000 https://www.percona.com/blog/?p=52469#comment-10971298 Hi , i have configured for Data replication but i dont see the process from select * from pg_stat_replication; , below is the output of the command,

na=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | c
lient_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush
_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_st
ate
—–+———-+———+——————+————-+—————–+–
———–+—————+————–+——-+———-+———–+——
—–+————+———–+———–+————+—————+——–
—-
(0 rows)

My database name is na but recovery.conf file got created under -P directory from /opt/postgres/data/-P

[root@meylvhpnaap02 data]# pwd
/opt/postgres/data

Please help on this

]]> By: Kavita https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10970863 Tue, 04 Jun 2019 23:18:57 +0000 https://www.percona.com/blog/?p=52469#comment-10970863 Hello Avinash, have you setup streaming replication in postgreSQL running in docker environment?

]]> By: Chk Pcs https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10970421 Thu, 07 Mar 2019 09:31:05 +0000 https://www.percona.com/blog/?p=52469#comment-10970421 Hi Avivallarapu
I’m really newbie to PostgreSQL, I’m confusing
Do we need to enabled archive_mode = on for streaming replication?

https://www.postgresql.org/docs/9.5/warm-standby.html

If you use streaming replication without file-based continuous archiving, the server might recycle old WAL segments before the standby has received them. If this occurs, the standby will need to be reinitialized from a new base backup. You can avoid this by setting wal_keep_segments to a value large enough to ensure that WAL segments are not recycled too early, or by configuring a replication slot for the standby. If you set up a WAL archive that’s accessible from the standby, these solutions are not required, since the standby can always use the archive to catch up provided it retains enough segments.

]]> By: Avinash Vallarapu https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10970386 Fri, 01 Mar 2019 14:45:47 +0000 https://www.percona.com/blog/?p=52469#comment-10970386 Hi Krishna, Hope you issue is resolved. You may not have used the data directory that is copied from Master. initdb generates a system identifier and the same gets copied to Slave. So, something went wrong with the copy.

]]> By: Avinash Vallarapu https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10970385 Fri, 01 Mar 2019 14:43:33 +0000 https://www.percona.com/blog/?p=52469#comment-10970385 Thank you Sergey. Good to know.

]]> By: Avinash Vallarapu https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10970384 Fri, 01 Mar 2019 14:43:08 +0000 https://www.percona.com/blog/?p=52469#comment-10970384 It would help too. You may read more on it in this blog post : https://www.percona.com/blog/2018/11/30/postgresql-streaming-physical-replication-with-slots/

]]> By: Sergey Gavrilov https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10970382 Fri, 01 Mar 2019 06:23:42 +0000 https://www.percona.com/blog/?p=52469#comment-10970382 Thank you Avinash Vallarapu! Nice explanation! It really save my day! Keep going in that way!))

]]> By: Priit https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10970200 Fri, 18 Jan 2019 06:06:05 +0000 https://www.percona.com/blog/?p=52469#comment-10970200 Hi, thanks for the great guide. But I have question regarding to physical replication slot. Is it useful to create this slot also or what are you thoughts ? I see in earlier versions it was used and the Definition itself says also, ” Replication slots are a crash-safe data structure which can be created
on either a master or a standby to prevent premature removal of
write-ahead log segments needed by a standby, as well as (with
hot_standby_feedback=on) pruning of tuples whose removal would cause
replication conflicts. “

]]> By: Krishna https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10970110 Mon, 24 Dec 2018 12:57:39 +0000 https://www.percona.com/blog/?p=52469#comment-10970110 HI Avinash,

Thanks for the detailed documentation, it helped me alot in my academic project.

I have tried the same on Windows environment and i am struggling to start SLAVE node (as i have moved MASTER node data to SLAVE). When I use the original data folder, able to start the SLAVE node, but when I do all the configurations, I am facing a challenge as shown below.

Error:
2018-12-24 11:17:31.235 IST [13104] FATAL: database system identifier differs between the primary and standby
2018-12-24 11:17:31.235 IST [13104] DETAIL: The primary’s identifier is 6637748246234064208, the standby’s identifier is 6637788080756493616.

]]> By: Avinash Vallarapu https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10970069 Mon, 17 Dec 2018 22:38:38 +0000 https://www.percona.com/blog/?p=52469#comment-10970069 You need to specify “WITH LOGIN” only when you use CREATE ROLE. As we have used CREATE USER … , it automatically assign LOGIN role to the replication user -> replicator (here in this post).

]]> By: cmennens https://www.percona.com/blog/setting-up-streaming-replication-postgresql/#comment-10969855 Tue, 23 Oct 2018 16:36:48 +0000 https://www.percona.com/blog/?p=52469#comment-10969855 When you create the replicator role, you MUST specify WITH LOGIN REPLICATION or you can’t do a pg_basebackup from the slave…

]]>