Thread: copy a database

copy a database

From
David Suela Fernández
Date:
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.


Re: copy a database

From
Christopher Browne
Date:
In an attempt to throw the authorities off his trail, dsuela@xsto.info (David Suela Fernández) transmitted:
> 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".

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=cbbrowne> 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

Re: copy a database

From
David Suela Fernández
Date:
El mié, 25-08-2004 a las 14:58, Christopher Browne escribió:
> In an attempt to throw the authorities off his trail, dsuela@xsto.info (David Suela Fernández) transmitted:
> > 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".
>
> 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

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.info>
xsto.info <el acceso a la información>


Re: copy a database

From
Oliver Elphick
Date:
On Wed, 2004-08-25 at 15:08, David Suela Fernández wrote:

> 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' 

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


Re: copy a database

From
Alvaro Herrera
Date:
On Wed, Aug 25, 2004 at 04:25:02PM +0100, Oliver Elphick wrote:
> On Wed, 2004-08-25 at 15:08, David Suela Fernández wrote:
>
> > 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'
>
> 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.

No, pg_user is a view on pg_shadow ...

SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb,
pg_shadow.usesuper, pg_shadow.usecatupd, '********'::text AS passwd,
pg_shadow.valuntil, pg_shadow.useconfig 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.


Re: copy a database

From
Alvaro Herrera
Date:
On Wed, Aug 25, 2004 at 12:09:58PM -0400, Alvaro Herrera wrote:

> No, pg_user is a view on pg_shadow ...
>
> SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb,
> pg_shadow.usesuper, pg_shadow.usecatupd, '********'::text AS passwd,
> pg_shadow.valuntil, pg_shadow.useconfig FROM pg_shadow;

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)


Re: copy a database

From
Oliver Elphick
Date:
On Wed, 2004-08-25 at 17:09, Alvaro Herrera wrote:
> On Wed, Aug 25, 2004 at 04:25:02PM +0100, Oliver Elphick wrote:

> > 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.
>
> No, pg_user is a view on pg_shadow ...

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


Re: copy a database

From
Tom Lane
Date:
David Suela =?ISO-8859-1?Q?Fern=E1ndez?= <dsuela@xsto.info> writes:
> 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

Have you tried re-creating that view?

CREATE VIEW pg_catalog.pg_user AS
    SELECT
        usename,
        usesysid,
        usecreatedb,
        usesuper,
        usecatupd,
        '********'::text as passwd,
        valuntil,
        useconfig
    FROM pg_shadow;

            regards, tom lane

Re: copy a database

From
Alvaro Herrera
Date:
On Wed, Aug 25, 2004 at 05:57:35PM +0100, Oliver Elphick wrote:
> On Wed, 2004-08-25 at 17:09, Alvaro Herrera wrote:
> > On Wed, Aug 25, 2004 at 04:25:02PM +0100, Oliver Elphick wrote:
>
> > > 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.
> >
> > No, pg_user is a view on pg_shadow ...
>
> 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.

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)


Re: copy a database

From
Oliver Elphick
Date:
On Wed, 2004-08-25 at 20:08, Alvaro Herrera wrote:
> On Wed, Aug 25, 2004 at 05:57:35PM +0100, Oliver Elphick wrote:
> > What I meant is that, since it is global,
...
> But pg_user is not global ...

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


Re: copy a database

From
Chris Travers
Date:
Have you tried pg_dump --no-owner in order to avoid getting the
ownership information?

David Suela Fernández wrote:

>El mié, 25-08-2004 a las 14:58, Christopher Browne escribió:
>
>
>>In an attempt to throw the authorities off his trail, dsuela@xsto.info (David Suela Fernández) transmitted:
>>
>>
>>>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".
>>>
>>>
>>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
>>
>>
>
>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.
>
>
>


Attachment

Re: copy a database

From
David Suela Fernández
Date:
El mié, 25-08-2004 a las 20:54, Tom Lane escribió:
> David Suela =?ISO-8859-1?Q?Fern=E1ndez?= <dsuela@xsto.info> writes:
> > 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
>
> Have you tried re-creating that view?
>
> CREATE VIEW pg_catalog.pg_user AS
>     SELECT
>         usename,
>         usesysid,
>         usecreatedb,
>         usesuper,
>         usecatupd,
>         '********'::text as passwd,
>         valuntil,
>         useconfig
>     FROM pg_shadow;
>
>             regards, tom lane

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.info>
xsto.info <el acceso a la información>


Re: copy a database

From
Tom Lane
Date:
David Suela =?ISO-8859-1?Q?Fern=E1ndez?= <dsuela@xsto.info> writes:
> It return:
> ERROR:  permission denied to create "pg_catalog.pg_user"
> DETAIL:  System catalog modifications are currently disallowed.
> How can i change this permissions?

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

Re: copy a database

From
Guy Fraser
Date:
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:

>El mié, 25-08-2004 a las 20:54, Tom Lane escribió:
>
>
>>David Suela =?ISO-8859-1?Q?Fern=E1ndez?= <dsuela@xsto.info> writes:
>>
>>
>>>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
>>>
>>>
>>Have you tried re-creating that view?
>>
>>CREATE VIEW pg_catalog.pg_user AS
>>    SELECT
>>        usename,
>>        usesysid,
>>        usecreatedb,
>>        usesuper,
>>        usecatupd,
>>        '********'::text as passwd,
>>        valuntil,
>>        useconfig
>>    FROM pg_shadow;
>>
>>            regards, tom lane
>>
>>
>
>It return:
>ERROR:  permission denied to create "pg_catalog.pg_user"
>DETAIL:  System catalog modifications are currently disallowed.
>
>How can i change this permissions?
>
>
--
Guy Fraser



Re: copy a database

From
David Suela Fernández
Date:
>
> 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.
>

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

Thanks a lot !!!

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