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 SESSION AUTHORIZATION 'bzptfhkw';
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'
SET SESSION AUTHORIZATION DEFAULT;
-- 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:
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:
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.