0

I have taken full backup of postgresql database which consists of 100 databases. The backup format is .sql (eg pg_dumpall.exe -U postgres > D:\Backup\fullbkp.sql) now one of my database got crashed and I want to extract this file to get that database backup only for restoration. I have searched a lot but couldn't find any way to decompress so that I can get that particular database from full backup file. Please suggest !!!! Regards Sadam

Sadamh Hussain
  • 1
  • 2
  • 6

1 Answers1

1

Such a backup is not compressed. Also, it contains a backup of all databases in th cluster, and there is no easy way to extract a single database.

Create a new PostgreSQL cluster with initdb, restore the dump there using psql, then use pg_dump to extract the single database you need.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks Laurenz , but is there any way to take full postgres backup in sql.gz format from powershell. – Sadamh Hussain May 17 '21 at 14:43
  • You'd have to take a "plain format" dump (`-F p`) and pipe it through `gzip`. But I recommend that you take a "custom format" dump (`-F c`), which is automatically compressed and can be restored with `pg_restore`. The only down side is that you can only dump individual databases that way; there is no custom format for `pg_dumpall`. – Laurenz Albe May 17 '21 at 14:55
  • It's quite tough for me as databases list is huge and if i took backup one by one it will kill my whole day. I need any alternative to take full backup it doesn't matter if it is in compressed format or not like .tar, .gz so that i can restore if anything happen with any particular database. – Sadamh Hussain May 18 '21 at 05:22
  • There is no way to take a backup of the whole cluster so that you can restore a single database from it. Either automate the procedure or do what I suggested in my answer: restore the whole cluster, then extract the database from that. – Laurenz Albe May 18 '21 at 06:02
  • I think there is some misunderstanding, I want to take full cluster backup like we take in linux using pg_dumpall -U postgres | gzip >/home/fullbkp.sql.gz and if we found any database got corrupted then we extract manually the backup file and then select the backup of corrupted database for restoration. – Sadamh Hussain May 18 '21 at 07:01
  • Right. There is no way to do that. – Laurenz Albe May 18 '21 at 07:11