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

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"
To:
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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: pg_dump/pg_restore question

Worik wrote:
> 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.

Almost correct. You're not actually connecting over IP at all, you'll be
using unix-sockets. Try a line something like:
local all all trust
Or, since you're on 7.2 probably
local all trust

HTH
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster