Re: pg_dump/pg_restore question

Hi Worik,

I can't address the specifics of your problem but I'll give you an example
of I do it on a 7.4 db. I dunno if all options are available on your
version.

To get the backup:

pg_dump --format=c -v DBNAME > FILENAME.dump

To restore you have to create a new DB (drop the old one first if
neccessary):

createdb -T template0 [-E ENCODING] DBNAME

Note that this is template 0 and not template 1. (also, don't forget to set
your database encoding if the default isn't what you want).

Then create a contents list:

pg_restore --list DBNAME.dump > DBNAME.list

You can edit the list file to rearrange the restore order or omit items.

Finally, do the restore:

pg_restore --use-list=DBNAME.list --dbname=DBNAME -v DBNAME.dump

This is all documented fairly well in the online documentation, you just
need to spend a bit of time going through it. As I recall, some information
is in the admin section and some is in the documentation of the client
programs (pg_dump and pg_restore are client programs iirc).

Also, consider upgrading to 7.4 to ensure you get the best support.

Good luck,
Iain

----- Original Message -----
From: "Worik" <worik(at)noggon(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Sent: Monday, August 23, 2004 11:59 AM
Subject: [SQL] pg_dump/pg_restore question

> Freinds
>
> I am new to this list, and this is my first message.
>
> I hope this is the correct forum, and the question not too stupid/simple.
>
> I have a database on a debian stable system...
>
> dpkg -l postgresql
> [snip]
> ii postgresql 7.2.1-2woody5 Object-relational SQL database,
> descended fr
>
> When I dump a database (as from the man page for pg_restore)
> pg_dump mydb > mydb.out
>
> OK. Get an SQL dump in mydb.out
>
> Create a new database....
>
> creatdb myotherdb
>
> Load the dump....
>
> psql -d myotherdb -f mydb.out
> psql:mydb.out:4: \connect: FATAL 1: IDENT authentication failed for
> user "postgres"
>
> I have tried adding the line
> host all 127.0.0.1 255.255.255.255 trust
>
> to pg_hba.conf but it makes no difference.
>
> The only thing I can do is edit the dump file into the part that wants
> to connect as postgres and the part that wants to connect as worik and
> run them under their respective logins.
>
> How can I set it up so I do not have to do that?
>
> What documentation should I be reading?
>
> Worik
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings