copy a database

[David Suela Fernández]

Hi!

Is it possible to copy/migrate one database from one postgres server to
other copying just the files in /var/lib/postgres/data/ ?

I can't use pg_dump because it give me an error. I think than the
instalation of postgres is corrupt because it give me a lot of error
like "ERROR: relation "pg_catalog.pg_ user" does not exist".

Thanks,

Suela.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Christopher Browne]

Re: copy a database

In an attempt to throw the authorities off his trail, dsuela@xsto.inf o (David Suela Fernández) transmitted:[color=blue]
> Is it possible to copy/migrate one database from one postgres server to
> other copying just the files in /var/lib/postgres/data/ ?
>
> I can't use pg_dump because it give me an error. I think than the
> instalation of postgres is corrupt because it give me a lot of error
> like "ERROR: relation "pg_catalog.pg_ user" does not exist".[/color]

Copying files from a corrupted database will just get you a _second_
corrupt database.

If it's corrupted, then the best you can do is to get what data you
can out of the database, and put it into a NEW database.

What you might try would be to dump it table by table:

for table in this_table that_table other_table; do
pg_dump -p $SOURCEPORT -t $table $SOURCEDB | psql -p $NEWPORT $NEWDB
done

If the database is corrupted, that's likely to partly break, but I
doubt you'll do much better any other way.
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrow ne> rate me

http://cbbrowne.com/info/lisp.html

"Natives who beat drums to drive off evil spirits are objects of scorn
to smart Americans who blow horns to break up traffic jams."
-- Unknown

[David Suela Fernández]

Re: copy a database

El mié, 25-08-2004 a las 14:58, Christopher Browne escribió:[color=blue]
> In an attempt to throw the authorities off his trail, dsuela@xsto.inf o (David Suela Fernández) transmitted:[color=green]
> > Is it possible to copy/migrate one database from one postgres server to
> > other copying just the files in /var/lib/postgres/data/ ?
> >
> > I can't use pg_dump because it give me an error. I think than the
> > instalation of postgres is corrupt because it give me a lot of error
> > like "ERROR: relation "pg_catalog.pg_ user" does not exist".[/color]
>
> Copying files from a corrupted database will just get you a _second_
> corrupt database.
>
> If it's corrupted, then the best you can do is to get what data you
> can out of the database, and put it into a NEW database.
>
> What you might try would be to dump it table by table:
>
> for table in this_table that_table other_table; do
> pg_dump -p $SOURCEPORT -t $table $SOURCEDB | psql -p $NEWPORT $NEWDB
> done[/color]

The problem is that pg_dump always give me the next error:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: relation "pg_user" does not exist
pg_dump: The command was: SELECT (SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, pg_encoding_to_ char(encoding) as encoding, datpath FROM pg_database WHERE datname = 'ez3'

I have the db scheme (tables and basic data), but what i need is the new
data of the db. I can access the db and make queries.

I think i'll make a script to pass data from the corrupt database to the
new one.

--
David Suela Fernández <dsuela@xsto.in fo>
xsto.info <el acceso a la información>


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Oliver Elphick]

Re: copy a database

On Wed, 2004-08-25 at 15:08, David Suela Fernández wrote:
[color=blue]
> The problem is that pg_dump always give me the next error:
>
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: relation "pg_user" does not exist
> pg_dump: The command was: SELECT (SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, pg_encoding_to_ char(encoding) as encoding, datpath FROM pg_database WHERE datname = 'ez3'[/color]

Maybe recreating pg_user in the database will help. It is a global
table, so if you have other databases where pg_user exists, copy the row
from pg_class in that database to pg_class in the corrupted database.

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
=============== =============== ==========
"But the day of the Lord will come as a thief in the
night. The heavens shall pass away with a great noise,
and the elements shall melt with fervent heat, and the
earth and the works that are therein shall be burned
up." II Peter 3:10


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Alvaro Herrera]

Re: copy a database

On Wed, Aug 25, 2004 at 04:25:02PM +0100, Oliver Elphick wrote:[color=blue]
> On Wed, 2004-08-25 at 15:08, David Suela Fernández wrote:
>[color=green]
> > The problem is that pg_dump always give me the next error:
> >
> > pg_dump: SQL command failed
> > pg_dump: Error message from server: ERROR: relation "pg_user" does not exist
> > pg_dump: The command was: SELECT (SELECT usename FROM pg_user WHERE
> > usesysid = datdba) as dba, pg_encoding_to_ char(encoding) as
> > encoding, datpath FROM pg_database WHERE datname = 'ez3'[/color]
>
> Maybe recreating pg_user in the database will help. It is a global
> table, so if you have other databases where pg_user exists, copy the row
> from pg_class in that database to pg_class in the corrupted database.[/color]

No, pg_user is a view on pg_shadow ...

SELECT pg_shadow.usena me, pg_shadow.usesy sid, pg_shadow.usecr eatedb,
pg_shadow.usesu per, pg_shadow.useca tupd, '********'::tex t AS passwd,
pg_shadow.valun til, pg_shadow.useco nfig FROM pg_shadow;

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Y una voz del caos me habló y me dijo
"Sonríe y sé feliz, podría ser peor".
Y sonreí. Y fui feliz.
Y fue peor.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Alvaro Herrera]

Re: copy a database

On Wed, Aug 25, 2004 at 12:09:58PM -0400, Alvaro Herrera wrote:
[color=blue]
> No, pg_user is a view on pg_shadow ...
>
> SELECT pg_shadow.usena me, pg_shadow.usesy sid, pg_shadow.usecr eatedb,
> pg_shadow.usesu per, pg_shadow.useca tupd, '********'::tex t AS passwd,
> pg_shadow.valun til, pg_shadow.useco nfig FROM pg_shadow;[/color]

Sorry, this is obviously missing the

CREATE VIEW pg_user AS

part.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"The important things in the world are problems with society that we don't
understand at all. The machines will become more complicated but they won't
be more complicated than the societies that run them." (Freeman Dyson)


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Oliver Elphick]

Re: copy a database

On Wed, 2004-08-25 at 17:09, Alvaro Herrera wrote:[color=blue]
> On Wed, Aug 25, 2004 at 04:25:02PM +0100, Oliver Elphick wrote:[/color]
[color=blue][color=green]
> > Maybe recreating pg_user in the database will help. It is a global
> > table, so if you have other databases where pg_user exists, copy the row
> > from pg_class in that database to pg_class in the corrupted database.[/color]
>
> No, pg_user is a view on pg_shadow ...[/color]

Fingers! view ... table ... thingy

What I meant is that, since it is global, recreating the item in
pg_class as a copy of the entry in another database should possibly get
rid of the pg_dump problem. If you create a new view, the result will
presumably not be restorable.

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
=============== =============== ==========
"But the day of the Lord will come as a thief in the
night. The heavens shall pass away with a great noise,
and the elements shall melt with fervent heat, and the
earth and the works that are therein shall be burned
up." II Peter 3:10


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Re: copy a database

David Suela =?ISO-8859-1?Q?Fern=E1ndez ?= <dsuela@xsto.in fo> writes:[color=blue]
> The problem is that pg_dump always give me the next error:[/color]
[color=blue]
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: relation "pg_user" does not exist[/color]

Have you tried re-creating that view?

CREATE VIEW pg_catalog.pg_u ser AS
SELECT
usename,
usesysid,
usecreatedb,
usesuper,
usecatupd,
'********'::tex t as passwd,
valuntil,
useconfig
FROM pg_shadow;

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Alvaro Herrera]

Re: copy a database

On Wed, Aug 25, 2004 at 05:57:35PM +0100, Oliver Elphick wrote:[color=blue]
> On Wed, 2004-08-25 at 17:09, Alvaro Herrera wrote:[color=green]
> > On Wed, Aug 25, 2004 at 04:25:02PM +0100, Oliver Elphick wrote:[/color]
>[color=green][color=darkred]
> > > Maybe recreating pg_user in the database will help. It is a global
> > > table, so if you have other databases where pg_user exists, copy the row
> > > from pg_class in that database to pg_class in the corrupted database.[/color]
> >
> > No, pg_user is a view on pg_shadow ...[/color]
>
> Fingers! view ... table ... thingy
>
> What I meant is that, since it is global, recreating the item in
> pg_class as a copy of the entry in another database should possibly get
> rid of the pg_dump problem. If you create a new view, the result will
> presumably not be restorable.[/color]

But pg_user is not global ...

alvherre=# select relname, relisshared from pg_class where relname = 'pg_user';
relname | relisshared
---------+-------------
pg_user | f
(1 fila)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"No deja de ser humillante para una persona de ingenio saber
que no hay tonto que no le pueda enseñar algo." (Jean B. Say)


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Oliver Elphick]

Re: copy a database

On Wed, 2004-08-25 at 20:08, Alvaro Herrera wrote:[color=blue]
> On Wed, Aug 25, 2004 at 05:57:35PM +0100, Oliver Elphick wrote:[color=green]
> > What I meant is that, since it is global,[/color][/color]
....[color=blue]
> But pg_user is not global ...[/color]

Yes; sorry, I was looking at the wrong thing.

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
=============== =============== ==========
"But the day of the Lord will come as a thief in the
night. The heavens shall pass away with a great noise,
and the elements shall melt with fervent heat, and the
earth and the works that are therein shall be burned
up." II Peter 3:10


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Chris Travers]

Re: copy a database

Have you tried pg_dump --no-owner in order to avoid getting the
ownership information?

David Suela Fernández wrote:
[color=blue]
>El mié, 25-08-2004 a las 14:58, Christopher Browne escribió:
>
>[color=green]
>>In an attempt to throw the authorities off his trail, dsuela@xsto.inf o (David Suela Fernández) transmitted:
>>
>>[color=darkred]
>>>Is it possible to copy/migrate one database from one postgres server to
>>>other copying just the files in /var/lib/postgres/data/ ?
>>>
>>>I can't use pg_dump because it give me an error. I think than the
>>>instalatio n of postgres is corrupt because it give me a lot of error
>>>like "ERROR: relation "pg_catalog.pg_ user" does not exist".
>>>
>>>[/color]
>>Copying files from a corrupted database will just get you a _second_
>>corrupt database.
>>
>>If it's corrupted, then the best you can do is to get what data you
>>can out of the database, and put it into a NEW database.
>>
>>What you might try would be to dump it table by table:
>>
>>for table in this_table that_table other_table; do
>> pg_dump -p $SOURCEPORT -t $table $SOURCEDB | psql -p $NEWPORT $NEWDB
>>done
>>
>>[/color]
>
>The problem is that pg_dump always give me the next error:
>
>pg_dump: SQL command failed
>pg_dump: Error message from server: ERROR: relation "pg_user" does not exist
>pg_dump: The command was: SELECT (SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, pg_encoding_to_ char(encoding) as encoding, datpath FROM pg_database WHERE datname = 'ez3'
>
>I have the db scheme (tables and basic data), but what i need is the new
>data of the db. I can access the db and make queries.
>
>I think i'll make a script to pass data from the corrupt database to the
>new one.
>
>
>[/color]



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[David Suela Fernández]

Re: copy a database

El mié, 25-08-2004 a las 20:54, Tom Lane escribió:[color=blue]
> David Suela =?ISO-8859-1?Q?Fern=E1ndez ?= <dsuela@xsto.in fo> writes:[color=green]
> > The problem is that pg_dump always give me the next error:[/color]
> [color=green]
> > pg_dump: SQL command failed
> > pg_dump: Error message from server: ERROR: relation "pg_user" does notexist[/color]
>
> Have you tried re-creating that view?
>
> CREATE VIEW pg_catalog.pg_u ser AS
> SELECT
> usename,
> usesysid,
> usecreatedb,
> usesuper,
> usecatupd,
> '********'::tex t as passwd,
> valuntil,
> useconfig
> FROM pg_shadow;
>
> regards, tom lane[/color]

It return:
ERROR: permission denied to create "pg_catalog.pg_ user"
DETAIL: System catalog modifications are currently disallowed.

How can i change this permissions?

--
David Suela Fernández <dsuela@xsto.in fo>
xsto.info <el acceso a la información>


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Re: copy a database

David Suela =?ISO-8859-1?Q?Fern=E1ndez ?= <dsuela@xsto.in fo> writes:[color=blue]
> It return:
> ERROR: permission denied to create "pg_catalog.pg_ user"
> DETAIL: System catalog modifications are currently disallowed.
> How can i change this permissions?[/color]

IIRC, you need to run a standalone backend, with either the -O or -P
switch (I forget which). The REINDEX man page has some tips about this.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Guy Fraser]

Re: copy a database

Have you tried to use "copy" to export the data from each table?

As a last resort you could try this, since you said you can still
select data from the tables.

Make a list of tables then :

sed -e "/^.*/copy & TO '&.sql';/" table.list | psql database

This should create a file for each table ending with ".sql".
Then re-create the table on the new DB and use "copy" to
import the data from the files.


David Suela Fernández wrote:
[color=blue]
>El mié, 25-08-2004 a las 20:54, Tom Lane escribió:
>
>[color=green]
>>David Suela =?ISO-8859-1?Q?Fern=E1ndez ?= <dsuela@xsto.in fo> writes:
>>
>>[color=darkred]
>>>The problem is that pg_dump always give me the next error:
>>>
>>>
>>>pg_dump: SQL command failed
>>>pg_dump: Error message from server: ERROR: relation "pg_user" does not exist
>>>
>>>[/color]
>>Have you tried re-creating that view?
>>
>>CREATE VIEW pg_catalog.pg_u ser AS
>> SELECT
>> usename,
>> usesysid,
>> usecreatedb,
>> usesuper,
>> usecatupd,
>> '********'::tex t as passwd,
>> valuntil,
>> useconfig
>> FROM pg_shadow;
>>
>> regards, tom lane
>>
>>[/color]
>
>It return:
>ERROR: permission denied to create "pg_catalog.pg_ user"
>DETAIL: System catalog modifications are currently disallowed.
>
>How can i change this permissions?
>
>[/color]
--
Guy Fraser



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[David Suela Fernández]

Re: copy a database

[color=blue]
>
> IIRC, you need to run a standalone backend, with either the -O or -P
> switch (I forget which). The REINDEX man page has some tips about this.
> [/color]

I run a standalone backend and create the view. Now it works perfectly.

Thanks a lot !!!

--
David Suela Fernández <dsuela@xsto.in fo>
xsto.info <el acceso a la información>


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)