Thank you for your all your videos. As a SQL Server DBA with an interest in PostgreSQL it’s extremely helpful that you share so much information in a nice and easy to understand way.
v12 PostgreSQL the move has been made, and the recovery.conf parameters are now part of postgresql.conf , recovery.conf (/etc/postgresql/12/main/postgresql.conf) triggered recovery mode. Since the file is gone new file have taken its place: recovery.signal: tells PostgreSQL to enter normal archive recovery it can be empty. just in case someone stumbles like i did :)
I am wondering why you are doing "psql -c select pg_switch_wal();"? Is it because you are trying to tell the Postgres to generate the WAL file even though it may not have reached 16MB?
Thanks for this tutorial, i have a query. While you were doing point-in-time recovery up to some specific time, you didn't use all recent Wal files from your achieve directory. You have only used the base backup files that only contains all the when you have taken the base backup and it did not contains other two row which you have added later.
If you do a PITR, doesn't all the data inserted after the restore point still get saved to the WAL files? So after the restore procedure, those WAL files still exist, right? Wouldn't that cause issues for future restores? You essentially wanted to get rid of that data, but if you were to do a new restore without specifying a restore point, the data would return.
Hi, I'm new to postgres. Have one question regarding your tutorial. If I created new user aside from postgres user, how can I run the wal archive for the new user?
hi, nice and clear explanation , but i have a small doubt because am new to this... what is systemctl in this command "sudo systemctl restart postgresql@10-main"
Hello. In the archive_command parameter where you put 'test ! -f /home/...' this 'test' is the name of the data base I have or leave it as you added it to the command line with the test itself?
After running the command in my pg-13 sudo service postgresql restart It could not restart After the command when I use ~$ psql It shows "error: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?"
Hi , default archive log file size is 16mb and can we reduce the size of archive log file.(i use the compressed version and it will reduce 16mb to 20k).but with out compression i need other solution .can u advise in this ?
Currently I use a pg_dump -> pg_restore strategy for a 4TB db, no fun. This tutorial convinced me to try continuous backup, thanks @Scaling Postgres. Once I do pg_basebackup it also saves my indices and materialized views, correct? Also, what should I do with the recovery.conf file after the db has been recovered? If I leave the file where it is, will it recover the db everytime I restart postgres? .. would not make sense...
Hi Michael- When recovery completed, recovery.conf file gets renamed and cluster gets opened for read write operation. Recovery.conf -----> recovery.done It happens automatically, you don't need to rename manually
Thanks for the tutorial and series. Great job. SQL Server guy, here, so there is some pain :\ Do you answer questions posted here - I see a few with no response...
@Zenina, update your pg_hba.conf with following lines(append the lines at the end of file), # Allow replication connections from localhost, by a user with the # replication privilege. local replication psql trust host replication psql 127.0.0.1/32 trust host replication psql ::1/128 trust After this please reload the process, pg_ctl stop -D /usr/local/pgsql/data/ pg_ctl start -D /usr/local/pgsql/data/ systemctl restart postgresql
Is it really necessary to use the restore_command if not doing a point in time recovery? The basebackup generates a full valid backup and so it should not be necessary to use the restore_command to use wal files from the archive folder, right?
In this case, he rotated his WAL, then made a basebackup, so it wouldn't actually need to replay any of the archived WAL files. If he'd have done a basebackup, then inserted some rows, then rotated the WAL, then deleted and restored the basebackup, then it would have actually used the archived WAL to insert the rows that were missing from the basebackup.
I’m trying to do incremental backup, For example when i do the backup for 3 rows and keep the backup inside folder as backup1, then the data inserted anther 3 rows (so i have now 6 rows inside table). After that i need to do another backup BUT i want only the LAST 3 rows inserted to be backed up as backup2 NOT the all 6 rows included inside the same table. can you explain how to do this
Essentially, taking a physical backup of the files and incrementally applying WAL files is an incremental backup. So as this video describes, take a backup and then keep all the WAL files produced. Then you can replay them on a restored database. The WAL files essentially are your incremental backup.
@@ScalingPostgres Does this mean you basically just make a cronjob with the select pg_switch_wal(); with the interval you prefer throughout the day? How does rotation and cleanup work?
@@joakimolsson5210 You don't have to use pg_switch_wal(). It will do it automatically. However, that lets you archive a WAL file manually. There is no rotation of the archive. It produces a constant stream of WAL files. It is up to you to clean them up periodically. What I tend to do is zip them up send them to a longer term storage like Amazon S3 and then delete them after a few days.
Hey, I Have been stuck after the "pg_basebackup -Ft -X none -D - | gzip > /var/lib/postgresql/db_file_backup.tar.gz" command line. it shows "pg_stop_backup still waiting for all required WAL segments to be archived (3840 seconds elapsed)" which means it has already taken 1 hour and continuing. Is it normal or just happening to me?
Thank you for your all your videos. As a SQL Server DBA with an interest in PostgreSQL it’s extremely helpful that you share so much information in a nice and easy to understand way.
A nice and clear explanation, if you could share the document in text format. It would be even more useful.
Thanks for the tutorail.
Hi , Im new to PostgreSQL.. but it's very easy to understanding concepts. thanks for your video.
Welcome!
v12 PostgreSQL the move has been made, and the recovery.conf parameters are now part of postgresql.conf ,
recovery.conf (/etc/postgresql/12/main/postgresql.conf) triggered recovery mode.
Since the file is gone new file have taken its place:
recovery.signal: tells PostgreSQL to enter normal archive recovery it can be empty.
just in case someone stumbles like i did :)
I added these parameters in postgresql.cfg but it didn't work.
restore_command = '/usr/sbin/nsroapprecover -o pg_p_opt="%p" -o pg_f_opt="%f" -z /nsr/apps/config/postgres.cfg'
recovery_target_time = '2021-11-04 14:18:00 +03'
recovery_target_inclusive = false
Thank you very much for a detail and informative tutorial
Hi, Thanks for sharing the wonderful information it's helping me to compare the operation in Postgres as I am oracle dba.
You are welcome!
Best explanation. Thank You
Thanks for tutorial and series, Great job
Thanks for your video. It's very helpful for me.
Thanks, great job and free. Awesome :)
Thanks for sharing. Very nice presentation.
Great explanation .thank you
I am wondering why you are doing "psql -c select pg_switch_wal();"? Is it because you are trying to tell the Postgres to generate the WAL file even though it may not have reached 16MB?
that's my guess too -- write partial WAL files to disk
Thank you for the great explanation.
Thanks so much...i really have enjoye this
man you helped me a lot
Thank you so much
Great video!! Thanks!!!!
Thanks for this tutorial, i have a query. While you were doing point-in-time recovery up to some specific time, you didn't use all recent Wal files from your achieve directory. You have only used the base backup files that only contains all the when you have taken the base backup and it did not contains other two row which you have added later.
If you do a PITR, doesn't all the data inserted after the restore point still get saved to the WAL files?
So after the restore procedure, those WAL files still exist, right? Wouldn't that cause issues for future restores?
You essentially wanted to get rid of that data, but if you were to do a new restore without specifying a restore point, the data would return.
This really helps. can this be done in AWS Aurora postgres serverless ?
thank you for the tutorial
Hi, I'm new to postgres. Have one question regarding your tutorial. If I created new user aside from postgres user, how can I run the wal archive for the new user?
hi, nice and clear explanation , but i have a small doubt because am new to this... what is systemctl in this command "sudo systemctl restart postgresql@10-main"
It's a Linux specific command for systemd to manage services.
nice explanation
Do managed databases in aws and gcp have this built-in ?
Thanks for the procedure. I am trying to do PITR for individual PostgreSQL. Please provide me the reference document to do.
Thanks
Sir did you install posters on Ubuntu .
Bcoz I have installed postegres in Windows and Ubuntu using virtual box
Thanks for the tutorial and i want know how we can add the recovery.conf to particular directory?
Hello. In the archive_command parameter where you put 'test ! -f /home/...' this 'test' is the name of the data base I have or leave it as you added it to the command line with the test itself?
Thank you. 👍
Can you please make a video to do postgres 13 incremental backup and replication in windows version
Thanks for your tutorial. When using pg_basebackup, it will not overwrite files if exists. How would you schedule recurring daily backup?
I use a shell script that grabs the current date and appends it to the file name that pg_basebackup writes to.
After running the command in my pg-13
sudo service postgresql restart
It could not restart
After the command when I use ~$ psql
It shows
"error: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?"
m doing same thing according to your video but my log file not saving in db_file_backup.can u help me please.what is the reason behind this .
Hi ,
default archive log file size is 16mb and can we reduce the size of archive log file.(i use the compressed version and it will reduce 16mb to 20k).but with out compression i need other solution .can u advise in this ?
Currently I use a pg_dump -> pg_restore strategy for a 4TB db, no fun. This tutorial convinced me to try continuous backup, thanks @Scaling Postgres. Once I do pg_basebackup it also saves my indices and materialized views, correct? Also, what should I do with the recovery.conf file after the db has been recovered? If I leave the file where it is, will it recover the db everytime I restart postgres? .. would not make sense...
Hi Michael-
When recovery completed, recovery.conf file gets renamed and cluster gets opened for read write operation.
Recovery.conf -----> recovery.done
It happens automatically, you don't need to rename manually
I did the same. But like ur video I did not get last 2 rows...like u didn't get..
Can Anybody explain why he uses this part " psql -c "select pg_switch_wal();" "? Is it necessary?
He switch the log to force wal archiving (change log file), it's just for the example.
@@venichka556 So you mean that it is not necessary?
Thanks for the tutorial and series. Great job.
SQL Server guy, here, so there is some pain :\
Do you answer questions posted here - I see a few with no response...
Do I have to place an entry in the pg_hba.conf? After running the pg_basebackup command, it says fatal: no entry in the pg_hba.conf....
@Zenina, update your pg_hba.conf with following lines(append the lines at the end of file),
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication psql trust
host replication psql 127.0.0.1/32 trust
host replication psql ::1/128 trust
After this please reload the process,
pg_ctl stop -D /usr/local/pgsql/data/
pg_ctl start -D /usr/local/pgsql/data/
systemctl restart postgresql
@@sagar9762 Still getting the same error
It worked, as my user as postgres not psql
Is it possible to make oracle RAC like solution in postgresql ?
Is it really necessary to use the restore_command if not doing a point in time recovery? The basebackup generates a full valid backup and so it should not be necessary to use the restore_command to use wal files from the archive folder, right?
In this case, he rotated his WAL, then made a basebackup, so it wouldn't actually need to replay any of the archived WAL files. If he'd have done a basebackup, then inserted some rows, then rotated the WAL, then deleted and restored the basebackup, then it would have actually used the archived WAL to insert the rows that were missing from the basebackup.
I’m trying to do incremental backup, For example when i do the backup for 3 rows and keep the backup inside folder as backup1, then the data inserted anther 3 rows (so i have now 6 rows inside table). After that i need to do another backup BUT i want only the LAST 3 rows inserted to be backed up as backup2 NOT the all 6 rows included inside the same table. can you explain how to do this
Essentially, taking a physical backup of the files and incrementally applying WAL files is an incremental backup. So as this video describes, take a backup and then keep all the WAL files produced. Then you can replay them on a restored database. The WAL files essentially are your incremental backup.
@@ScalingPostgres Does this mean you basically just make a cronjob with the select pg_switch_wal(); with the interval you prefer throughout the day? How does rotation and cleanup work?
@@joakimolsson5210 You don't have to use pg_switch_wal(). It will do it automatically. However, that lets you archive a WAL file manually. There is no rotation of the archive. It produces a constant stream of WAL files. It is up to you to clean them up periodically. What I tend to do is zip them up send them to a longer term storage like Amazon S3 and then delete them after a few days.
tar backup is working .
Great!
hello, one question, is it possible to recover the database after applying a drop to the entire database?
Only if you have a backup.
Hey, I Have been stuck after the "pg_basebackup -Ft -X none -D - | gzip > /var/lib/postgresql/db_file_backup.tar.gz" command line. it shows "pg_stop_backup still waiting for all required WAL segments to be archived (3840 seconds elapsed)" which means it has already taken 1 hour and continuing. Is it normal or just happening to me?
Did you find a solution to this?
This is a simple fix actually, just remove all files in the pg_log_archive