PostgreSQL Streaming Replication

Learn how to setup a replica of your PostgreSQL database. This replica will stream changes from the primary database cluster and supports read-only queries. This replica can also be promoted to be the new primary database. We show you each of these features in this tutorial.

Want to learn more about Postgres performance?

[ronsearch]

Your explanations are clear and pragmatic. Thanks so much for sharing your knowledge.

[terryburton]

Small correction. At 15:30 you mention that standby_mode allows read-only connections to a host that is in recovery, which is in fact the purview of the separate, global hot_standby setting. What standby_mode really does is to cause the replica to continue to replicate changes on the master once it has caught up - essentially to be an ongoing replica rather than perform a one-shot catch up.

[vimsen]

Please Ignore my earlier comment. I set the password. This is an excellent tutorial.

[ghostpup]

Hi, some small comments.
in the primary_conninfo line between "host" and "/ var / run / postgresql" there must be a "=" sign (The error is in the documentation, which is in the description).

A small note for lamers (like me) who want to apply this instruction to version 12 of postgres. The recovery.conf file is no longer available in version 12. Settings from it are transferred to replica`s postgresql.conf (everything except standby_mode). And also you need to check for an empty (before starting replication) file

The rest of the guide is cool

[frikabg]

5:51 if you follow the instructions that you have posted on the link in your description the commands: sudo -H -u postgres mkdir -p are without -p at the end which makes them invalid (at least for me). I hope this helps someone. Also notice that in the vid the example given is with replica while in the link it is REPLICA1.

[gulledhaydar]

I have spent a couple of hours troubleshooting the recovery.conf file you used in your example. I did not know that it had been replaced with the recovery.signal file from v12 of postgresql.

[kakarnyori]

Hi! I was just looking for a nice tutorial on PostgreSQL replication, and this video was very informative, and so I subscribed! Thank you! Could you also please make a video tutorial on replication between separate servers (master on different server and slave on another) and logical streaming replication, please. And please keep up the good work.

[chjanardhanreddy]

Hi Creston, That was clear and crystal. I am just looking for Cluster set up for Postgresql like an oracle .
When Master went down Slave become active and act as Master,
Like Same when Master comes up does the Transaction will come back to the Main Master node or Transactions will go to Slave(Acting like Master) node only please confirm. Thanks in Advance.

[Yoga-mit-Waltraud-b]

You lost me at line 63. More graphics and explaination may have helped. At least I now know how to pronouce "ubuntu" - Thanks. P.S. The command list was great.

[atul]

how archive file are getting copied in "/pg_log_archive/replica/" directory because in postgresql.conf file for main cluster I can't see the archive command for this path ? please tell me.

[markshay]

This video has been great but running into a slight issue.. When I bring up the replica Node I am get the following error in the log


2020-04-27 00:12:26.278 EDT [3084] LOG: archive command failed with exit code 1
2020-04-27 00:12:26.278 EDT [3084] DETAIL: The failed archive command was: test ! -f && cp


## The achive command is set to below:


archive_command = 'test ! -f && cp %p


Please advise

[Mladenac]

pg_lsclusters works on Debian 10 with pg 13 installed

[jaspindersingh]

Is there a way to automatically delete archives on Master Server as it keeps on storing data and filling up my HD?

[suend]

When using pg_basebackup to copy to replica folder, it gave me error saying /data2/space2 folder exists but not empty. This folder is used for a tablespace in the main database. If I drop the tablespace and the command will have no issue. How can I create replica if I have tablespaces defined in the main instance?
Thanks!

[gotmb]

This tutorial, is it using streaming replication + log shipping?

[jamesyu]

I wanted to try this with my Postgres v11. But I couldn't find the pg_lsclusters command under the bin folder. Am I missing some package to install? What RPM installs those pg cluster commands?

[malymohsem]

Is streaming replication active/active replication?

[Bigheadbear]

How do you set the primary_conninfo in this tutorial? 16:49
I stuck here, it is not written in the code example and cannot be viewed in this video either.

[zefanyarichen]

my streaming replication was running for once, but now isn't work, after i check the log,
it show FATAL: could not receive data from WAL stream: ERROR: requested WAL segment has already been removed
any advice what should i do?

[andersonmillroad]

Hi, Great Tutorial. I have a slight different requirements. I want to streaming backup server but only for specific databases. Is that possible?