Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2011

    Question Unanswered: Errors while restoring a postgresql database from sql dump file


    I have to restore a postgreSQL database to multiple development servers running postgreSQL 8.4.x on Windows XP, Debian Linux and OpenSuSE (x differs between them).

    I did not create the dump myself and it may have been created with an older version of ppstgreSQL. It is a 1 GB plain text file named database.sql. I need the DB structure (tables, users, functions) but not necessarily the data contained in the file (I would actually prefer not to have it).

    Here is the output of head -n 25 database.sql:
    -- PostgreSQL database dump
    SET client_encoding = 'SQL_ASCII';
    SET check_function_bodies = false;
    SET search_path = public, pg_catalog;
    -- TOC entry 170 (OID 17143)
    -- Name: plpgsql_call_handler(); Type: FUNC PROCEDURAL LANGUAGE; Schema: public; Owner: bzptfhkw
    CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
        AS '$libdir/plpgsql', 'plpgsql_call_handler'
        LANGUAGE c;
    -- TOC entry 169 (OID 17144)
    The program pg_restore won't read the file (it says 'pg_restore: [archiver] input file does not appear to be a valid archive', which is obviously true). So I created a database and a user for it, then I tried to restore using psql like this:

    postgres@dev:~$ psql -U <user> -d <database> -f database.sql
    This seems to have worked more or less, but 2 questions/problems remain:

    1) I got several of these error messages during the restore even though I ran the command as the postgres user:
    ERROR: permission denied to set session authorization
    ERROR: permission denied for language c
    WARNING: no privileges were granted for "public"
    How can I avoid these errors?

    EDIT#1: Nevermind, if I use -U <user> I am obviously not the postgres user anymore. Using -U postgres (or just omitting the -U altogether) removed the errors.

    2) The command restored everything including the data. It took ages and also required lots of manual fiddling (creating the DB, figuring out which other roles are required and creating them etc). Is there any way to avoid this in the future? I guess not with the SQL file. So the proper way would be to create a dump using pg_dump now and restore from that in the future? Or how should I do it?

    EDIT#2: I managed to get it done, at least almost, like this:
    I restored the DB from the .sql file, then I used pg_dump to make a backup of the structure:
    pg_dump --schema-only --format=custom --file=mydb_schema_only.pg_dump mydb
    I can now restore the schema like this:
    postgres@srv> psql
      psql> CREATE ROLE mydb WITH LOGIN;
      psql> CREATE DATABASE mydb OWNER mydb;
      psql> \q
    postgres@srv> pg_restore --dbname=mydb mydb_schema_only.pg_dump
    So far, so good. But I'm still looking for a way where I do not need to manually create the DB and role before being able to restore.

    Thanks a lot for your time in advance,

    Last edited by dfsp_spirit; 02-21-11 at 16:25. Reason: Figured out question #1 myself. :)

  2. #2
    Join Date
    May 2008
    If you use pg_dump to create a tar or custom file, then you can specify what you want restored:

    # full database dump
    pg_dump --format custom -f mydb
    # restore schema only
    pg_restore -N -d mydb
    # restore data only
    pg_restore -a -d mydb
    # create database before restoring
    pg_restore -C -d mydb
    This is, of course, all described in the documentation for the pg_dump and pg_restore commands.

    I don't believe there's any way to dump and restore a role, unless you use the pg_dumpall command, which dumps your entire server cluster.

  3. #3
    Join Date
    Feb 2011

    Thumbs up

    Thanks for your reply!

    So there is no way to do this (except dump the whole cluster which is of course not at all what I want)?

    Imagine I was a company who sold these webpacks (webspace, maybe 5 DBs and 5 DB users) and I wanted to move half of all the DBs to a second server. I would have to figure out all the users belonging to the databases manually?

    I expected that there was some command line switch for pg_dump to tell it to backup a database including dependencies like roles but I can't find it.

    On the first part: as I said I didn't create the dump and it was not in custom format so I couldn't use pg_restore, but I solved that as described above.

Tags for this Thread

Posting Permissions

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