Thread: Postgresql Backups
Hello all To backup my postgresql data I use the pg_dumpall command which dumps all my databases to a .sql file. If I would like to restore a single database from this file how would I do this? Is it possible using PgAdmin - as this only allows us to restore a database from a .backup file??? When trying to do from the command prompt I get the following errors: SET SET SET SET SET WARNING: no privileges could be revoked for "public" REVOKE WARNING: no privileges could be revoked for "public" REVOKE WARNING: no privileges were granted for "public" GRANT WARNING: no privileges were granted for "public" GRANT And it doesnt restore my database. Any help would be greatly appreciated! -- View this message in context: http://www.nabble.com/Postgresql-Backups-tp24845786p24845786.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 06/08/2009 13:42, sub_woofer wrote: > Hello all > > To backup my postgresql data I use the pg_dumpall command which dumps all my > databases to a .sql file. > > If I would like to restore a single database from this file how would I do > this? Is it possible using PgAdmin - as this only allows us to restore a > database from a .backup file??? You'll have to edit the dump file and remove everything except the database you want to restore. Be aware that pg_dumpall also dumps cluster-wide objects, such as roles, which pg_dump doesn't dump. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
On Thu, Aug 6, 2009 at 6:42 AM, sub_woofer<tqzelijah@yahoo.co.uk> wrote: > > Hello all > > To backup my postgresql data I use the pg_dumpall command which dumps all my > databases to a .sql file. > > If I would like to restore a single database from this file how would I do > this? Is it possible using PgAdmin - as this only allows us to restore a > database from a .backup file??? You're probably better off switching to explicitly backing up databases in the custom format and using a single pg_dumpall --globals to backup the users accounts and such. That's how I prefer to do it and it makes life much easier.
On Fri, Aug 07, 2009 at 01:54:47PM -0600, Scott Marlowe wrote:
> You're probably better off switching to explicitly backing up
> databases in the custom format and using a single pg_dumpall --globals
> to backup the users accounts and such.
-g
--globals-only
Dump only global objects (roles and tablespaces), no databases.
On Fri, Aug 7, 2009 at 3:54 PM, Scott Marlowe<scott.marlowe@gmail.com> wrote: > You're probably better off switching to explicitly backing up > databases in the custom format and using a single pg_dumpall --globals > to backup the users accounts and such. That's how I prefer to do it > and it makes life much easier. This is precisely how we do it. Make for recovering certain data easier, especially when you have a lot of databases on your server.
Hello all Thank you very much for your responses! I realised that the restores were not working as the databases were not being...backed up!...oops! After I did the reinstallation of the OS I forgot to give permissions in postgresql for the user doing the backup in ubuntu! I have fixed this. I have also listened to all your advice and decided to do single dumps of each of the databases as well as a pg_dumpall of the globals. But still the issue remains how do you perfom a restore (not using the command line that I know how to do and works successfully now!), but via pgAdmin. As when I click on restore after selecting the file the okay button is still disabled....any ideas???? Thanks again! t. -- View this message in context: http://www.nabble.com/Postgresql-Backups-tp24845786p24898427.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.