Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    16

    Unanswered: Postgresql Restores

    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!

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    PgAdmin just defaults to looking for a file with the .backup extension but will happily take your .sql file.

    You've got a couple warnings but no errors, nothing that would cause it to abort the transaction. Are you sure you are dumping the data and schema and not just the schema?

    Also, I'd recommend dumping a single database or even schema per file as it will make your life easier when something goes awry and you need to do an emergency restore.

  3. #3
    Join Date
    Apr 2009
    Posts
    16
    Thanks artacus72 for your response!

    The reason why it wasnt restoring after reinstallation of the o/s is beacuse the user that was executing the backups didnt have correct permissions set in postgresql therefore the databases werent being backed up!!! silly me!

    But with PGAdmin, when I click on restore (when right clicking on the database) and then locate the .sql file the ok button is still disabled???....so unless i do the restores via the command line psql name_of_db_to_restore < path_of_sql_file i cant do a restore, but I would like to make it as user friendly as possible....

    t.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •