how to decompress .sql extension file in windows server

Question

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

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

• Sadamh Hussain: Thanks Laurenz , but is there any way to take full postgres backup in sql.gz format from powershell.
• Laurenz Albe: 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.
• Sadamh Hussain: 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.
• Laurenz Albe: 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.
• Sadamh Hussain: 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.