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 :)
It's now done in the main postgresql.conf file and touching a recovery.signal file in the main directory prior to restarting the database service. All other steps are still applicable.
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?
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?
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"?"
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.
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"
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 ?
@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
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
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.
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...
![Seungmin "그렇게, 천천히, 우리(As we are)" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/kAzmhLHePqU/mqdefault.jpg)
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.
Really useful ! Thanks !
Hi , Im new to PostgreSQL.. but it's very easy to understanding concepts. thanks for your video.
Welcome!
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, thank you very much! It is interesting, that for me the timeline was not changed after restating pg. Do you know why?
Thank you its very informative. could you please post for postgres 17 full and incremental backup that will be very helpful
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!
man you helped me a lot
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
Best explanation. Thank You
Thanks for tutorial and series, Great job
Nicely done. I wonder, do these exact steps still work in PostgreSQL 17? Time to experiment!
Replying to myself - not it isn't:
FATAL: using recovery command file "recovery.conf" is not supported
It's now done in the main postgresql.conf file and touching a recovery.signal file in the main directory prior to restarting the database service.
All other steps are still applicable.
Thanks for your video. It's very helpful for me.
This really helps. can this be done in AWS Aurora postgres serverless ?
Thanks, great job and free. Awesome :)
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
Thanks for sharing. Very nice presentation.
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?
Great explanation .thank you
Thanks so much...i really have enjoye this
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?
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"?"
Great video!! Thanks!!!!
Do managed databases in aws and gcp have this built-in ?
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.
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.
Sir did you install posters on Ubuntu .
Bcoz I have installed postegres in Windows and Ubuntu using virtual box
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 ?
Can you please make a video to do postgres 13 incremental backup and replication in windows version
Thank you for the great explanation.
Thanks for the procedure. I am trying to do PITR for individual PostgreSQL. Please provide me the reference document to do.
Thanks
Thank you so much
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 ?
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 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.
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 .
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.
thank you for the tutorial
nice explanation
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.
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...
Thank you. 👍
I did the same. But like ur video I did not get last 2 rows...like u didn't get..
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.