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.
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.
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 "/var/lib/postgresql/12/replica/standby.signal" The rest of the guide is cool
5:51 if you follow the instructions that you have posted on the link in your description the commands: sudo -H -u postgres mkdir /var/lib/postgresql/pg_log_archive/main -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.
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.
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.
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.
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!
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?
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 /var/lib/postgresql/pg_log_archive/main/00000001000000000000000C && cp pg_wal/00000001000000000000000C /var/lib/postgresql/pg_log_archive/replica/00000001000000000000000C ## The achive command is set to below: archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/main/%f && cp %p /var/lib/postgresql/pg_log_archive/replica/%f' Please advise
This turned out be a very simple solution. I had recovery.conf in the wrong directory. Doh! I had the file in /etc/postgresql/10/main/recovery.conf when it should have been in the data directory: /var/lib/postgresql/10/main
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 000000010000029700000076 has already been removed any advice what should i do?
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.
Hi Hsin. In the video, I scroll over to the right to show the full line. You can pause the video there. Also, I did update the text part of the tutorial to include all lines at scalingpostgres.com.
@@ScalingPostgres Thanks I've got it. Now I have another problem. When I tried to restart the replica1 cluster with this commnad "pg_ctlcluster 12 replica1 start" . It gave me this error: pg_ctl: could not start server Examine the log output.FATAL: using recovery command file "recovery.conf" is not supported How do I fix this ? I am running PostgreSQL 12 on Ubuntu 18.04
@@Bigheadbear666 This tutorial won't work for PG 12 and higher. Most of the recovery.conf settings have been moved into the main postgresql.conf file and then you have to use signal files instead. I would consult another tutorial or the Postgres documentation until I am able to publish an updated one.
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.
When I tried to start the replica ( Line 75 ) the system is asking for the password of postgres. I have not set a password when I installed postgres. It does not accept my Ubuntu sudo password.
can you explain the meaning of %f and %p , should i replace those with something else ? i have seen lots of videos and resources, they do not explain the meaning of %f or %p or if those should be replaced with something else . Your Video is very good. Thank you for your time
This is explained in the postgres docs for the archive command: "In archive_command, %p is replaced by the path name of the file to archive, while %f is replaced by only the file name." Link: www.postgresql.org/docs/13/continuous-archiving.html
@@ScalingPostgres Thank you very much. I already understood that and applied the streaming replication between two windows machines ( primary and secondary ) Thanks again.
I run this command: sudo pg_ctlcluster 14 replica start result : Job for postgresql@14-replica.service failed because the service did not take the steps required by its unit configuration. See "systemctl status postgresql@14-replica.service" and "journalctl -xe" for details. please help me.
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.
Your explanations are clear and pragmatic. Thanks so much for sharing your knowledge.
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.
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 "/var/lib/postgresql/12/replica/standby.signal"
The rest of the guide is cool
5:51 if you follow the instructions that you have posted on the link in your description the commands: sudo -H -u postgres mkdir /var/lib/postgresql/pg_log_archive/main -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.
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.
Yes that would be indeed helpful. I'm looking for a way to make my UAT server a slave of the production database.
Hey Kakar, did you get how data will be replicate on different server?…if you know..please help me now. It’s urgent.
Please Ignore my earlier comment. I set the password. This is an excellent tutorial.
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.
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.
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!
Thanks very much!
Is streaming replication active/active replication?
Is there a way to automatically delete archives on Master Server as it keeps on storing data and filling up my HD?
This tutorial, is it using streaming replication + log shipping?
thank you very much
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?
It is ubuntu specific, he said it in the presentation
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 /var/lib/postgresql/pg_log_archive/main/00000001000000000000000C && cp pg_wal/00000001000000000000000C /var/lib/postgresql/pg_log_archive/replica/00000001000000000000000C
## The achive command is set to below:
archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/main/%f && cp %p /var/lib/postgresql/pg_log_archive/replica/%f'
Please advise
This turned out be a very simple solution. I had recovery.conf in the wrong directory. Doh!
I had the file in /etc/postgresql/10/main/recovery.conf when it should have been in the data directory: /var/lib/postgresql/10/main
Thanks
can u demo streaming replication using barman tool ?
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 000000010000029700000076 has already been removed
any advice what should i do?
You need to extend the amount of wal segments retained or use a replication slot so that the primary does not remove needed wal segments
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.
Hi Hsin. In the video, I scroll over to the right to show the full line. You can pause the video there. Also, I did update the text part of the tutorial to include all lines at scalingpostgres.com.
@@ScalingPostgres Thanks I've got it.
Now I have another problem. When I tried to restart the replica1 cluster with this commnad "pg_ctlcluster 12 replica1 start" . It gave me this error:
pg_ctl: could not start server
Examine the log output.FATAL: using recovery command file "recovery.conf" is not supported
How do I fix this ?
I am running PostgreSQL 12 on Ubuntu 18.04
@@Bigheadbear666 This tutorial won't work for PG 12 and higher. Most of the recovery.conf settings have been moved into the main postgresql.conf file and then you have to use signal files instead. I would consult another tutorial or the Postgres documentation until I am able to publish an updated one.
@@ScalingPostgres Thanks a lot, looking forward to it.
Hi, Great Tutorial. I have a slight different requirements. I want to streaming backup server but only for specific databases. Is that possible?
Yes, with logical replication.
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.
When I tried to start the replica ( Line 75 ) the system is asking for the password of postgres. I have not set a password when I installed postgres. It does not accept my Ubuntu sudo password.
Same problem
Hello christine, can we make slave server to accept data from multiple master serveres
I am using postgresql 9.6
pg_lsclusters works on Debian 10 with pg 13 installed
can you explain the meaning of %f and %p , should i replace those with something else ? i have seen lots of videos and resources, they do not explain the meaning of %f or %p or if those should be replaced with something else .
Your Video is very good. Thank you for your time
This is explained in the postgres docs for the archive command: "In archive_command, %p is replaced by the path name of the file to archive, while %f is replaced by only the file name." Link: www.postgresql.org/docs/13/continuous-archiving.html
@@ScalingPostgres Thank you very much. I already understood that and applied the streaming replication between two windows machines ( primary and secondary )
Thanks again.
Thanks! Great video! Just one thing. Please pronounce Ubuntu as "oo-BOON-too", not "you-BUNT-oo".
I run this command: sudo pg_ctlcluster 14 replica start
result : Job for postgresql@14-replica.service failed because the service did not take the steps required by its unit configuration.
See "systemctl status postgresql@14-replica.service" and "journalctl -xe" for details.
please help me.