PostgreSQL Backup & Point-In-Time Recovery

[@RU-qv3jl]

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.

[@YogeshKushwahaindia]

A nice and clear explanation, if you could share the document in text format. It would be even more useful. Thanks for the tutorail.

[@balvanthreddy]

Hi , Im new to PostgreSQL.. but it's very easy to understanding concepts. thanks for your video.

[@ScalingPostgres]

Welcome!

[@code7bit301]

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 :)

[@uqroztrk]

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

[@J4cko999]

Really useful ! Thanks !

[@shantanushekharsjunerft9783]

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?

[@JustinPierce01]

that's my guess too -- write partial WAL files to disk

[@unmatal]

Thank you very much for a detail and informative tutorial

[@akk2766]

Nicely done. I wonder, do these exact steps still work in PostgreSQL 17? Time to experiment!

[@akk2766]

Replying to myself - not it isn't: FATAL: using recovery command file "recovery.conf" is not supported

[@akk2766]

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.

[@ikmaljohari1367]

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?

[@Draz-]

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.

[@mahesh3267]

This really helps. can this be done in AWS Aurora postgres serverless ?

[@AndeerStar]

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?

[@konireddylavanya5486]

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"

[@MichaelJendryke]

It's a Linux specific command for systemd to manage services.

[@sostenesapollo]

Do managed databases in aws and gcp have this built-in ?

[@md.imrulhasan8757]

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"?"

[@badaldudy9624]

Thanks for tutorial and series, Great job

[@vikramgodara8468]

Hi, Thanks for sharing the wonderful information it's helping me to compare the operation in Postgres as I am oracle dba.

[@ScalingPostgres]

You are welcome!

[@chiragkarkera1]

Best explanation. Thank You

[@leon6295]

Can you please make a video to do postgres 13 incremental backup and replication in windows version

[@vishwas22khare]

Sir did you install posters on Ubuntu . Bcoz I have installed postegres in Windows and Ubuntu using virtual box

[@lalithadurga9677]

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 ?

[@zr7927]

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....

[@sagar9762]

@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

[@varuntaneja4654]

@@sagar9762 Still getting the same error

[@varuntaneja4654]

It worked, as my user as postgres not psql

[@samilekesiz1354]

Thanks, great job and free. Awesome :)

[@bhanukonthala6223]

Thanks for the tutorial and i want know how we can add the recovery.conf to particular directory?

[@vedraj7247]

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.

[@srilinux2490]

Thanks for the procedure. I am trying to do PITR for individual PostgreSQL. Please provide me the reference document to do. Thanks

[@frankchen9264]

Thanks for your video. It's very helpful for me.

[@abhishekyadava]

Thanks for sharing. Very nice presentation.

[@suend]

Thanks for your tutorial. When using pg_basebackup, it will not overwrite files if exists. How would you schedule recurring daily backup?

[@ScalingPostgres]

I use a shell script that grabs the current date and appends it to the file name that pg_basebackup writes to.

[@muhanadtur8785]

Thank you for the great explanation.

[@skizzi86]

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?

[@ellispercival6177]

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.

[@amandhaka20002]

Is it possible to make oracle RAC like solution in postgresql ?

[@amardeepkumar9301]

Great explanation .thank you

[@mohitchauhan4154]

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 .

[@MichaelJendryke]

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...

[@Piyushmishrabanaras]

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

[@f5edy117]

man you helped me a lot

[@calebmbugua745]

Thanks so much...i really have enjoye this

[@tmsa771]

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

[@ScalingPostgres]

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.

[@joakimolsson5210]

@@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?

[@ScalingPostgres]

@@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.

[@gusjoagomez]

Great video!! Thanks!!!!

[@rahulkarmakar1736]

I did the same. But like ur video I did not get last 2 rows...like u didn't get..

[@ab5sr]

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...

[@violetsania7314]

thank you for the tutorial

[@ranvijaymehta]

Thank you so much

[@shiwanginishishulkar4744]

nice explanation

[@voffknur]

Thank you. 👍

[@rodrigomendoza1060]

hello, one question, is it possible to recover the database after applying a drop to the entire database?

[@ScalingPostgres]

Only if you have a backup.

[@mohitchauhan4154]

tar backup is working .

[@ScalingPostgres]

Great!