Thread: Can't import databases with pg_dump. Why?
Hi. I'm trying to import some databases I had on redhat 7.2 (think it had postgressql-7.1.x series but not sure) to my new Debian woody installation (postgresql 7.2.1-2woody2). I created a database called 'famiglia' under the postgres user. Then, always by postgres user i did: gaia:/etc/postgresql$ pg_dump famiglia < /mnt/redhat/home/postgres_postgres \ /famiglia.dump and this is what I got as a result: -- -- Selected TOC Entries: -- \connect - postgres -- -- TOC Entry ID 2 (OID 16556) -- -- Name: "plpgsql_call_handler" () Type: FUNCTION Owner: postgres -- CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS '$libdir/plpgsql', 'plpgsql_call_handler' LANGUAGE 'C'; -- -- TOC Entry ID 3 (OID 16557) -- -- Name: plpgsql Type: PROCEDURAL LANGUAGE Owner: -- CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER ''; gaia:/etc/postgresql$ Obviuosly there are no relations in the database because the import failed somewhere and I can't figure out where. I'm not getting any deubg info from /var/log/postgresql.log even though I've set: debug_level = 1 debug_print_query = on log_connections = on log_pid = on log_timestamp = on syslog = 2 # if syslog is 0, turn silent_mode off! silent_mode = off syslog_facility = LOCAL0 trace_notify = off max_connections = 64 # shared_buffers must be at least twice max_connections, and not less # than 16 shared_buffers = 128 # TCP/IP access is allowed by default, but the default access given in # pg_hba.conf will permit it only from localhost, not other machines. tcpip_socket = 1 in /etc/postgresql/postgresql.conf and: local all ident sameuser host all 127.0.0.1 255.0.0.0 ident sameuser host all 0.0.0.0 0.0.0.0 reject host all 192.168.0.3 255.255.255.0 trust in /etc/postgresql/pg_hba.conf. The only info I get form the log file are 2 lines: FindExec: found "/usr/lib/postgresql/bin/postgres" using argv[0] FindExec: found "/usr/lib/postgresql/bin/postmaster" using argv[0] when I give '/etc/init.d/postgresql restart'. Any suggestions on what's happening? and how I can solve this. It would be a tedious work to create all databases from scratch and import all tables in the new databases..... Thanyou and bye. Antonio G.
"Antonio G. - Geotronix" <nightnavigator@libero.it> writes: > Hi. > > I'm trying to import some databases I had on redhat 7.2 (think it had > postgressql-7.1.x series but not sure) to my new Debian woody > installation (postgresql 7.2.1-2woody2). I created a database called > 'famiglia' under the postgres user. Then, always by postgres user i did: > > gaia:/etc/postgresql$ pg_dump famiglia < /mnt/redhat/home/postgres_postgres \ > /famiglia.dump Ummm.... You don't use pg_dump to restore. -Doug
"Antonio G. - Geotronix" <nightnavigator@libero.it> writes:
> Obviuosly there are no relations in the database because the import
> failed somewhere and I can't figure out where. I'm not getting any deubg
> info from /var/log/postgresql.log even though I've set:
> debug_level = 1
> debug_print_query = on
> log_connections = on
> log_pid = on
> log_timestamp = on
> syslog = 2
Doesn't syslog = 2 mean "send output only to syslog"?
> in /etc/postgresql/pg_hba.conf. The only info I get form the log file
> are 2 lines:
> FindExec: found "/usr/lib/postgresql/bin/postgres" using argv[0]
> FindExec: found "/usr/lib/postgresql/bin/postmaster" using argv[0]
These messages are (erroneously) printed on stderr always. But
everything interesting goes to elog, which you've directed to syslog.
Look in your syslog log file.
regards, tom lane
"Antonio G. - Geotronix" <antonio@sunstone.it> writes: > In your reply you told me that I'm not using pg_dump to restore. But as > you can see in my previus message I am using pg_dump :-) Well, it's wrong. ;) I phrased my response badly--I meant "You shouldn't try to use pg_dump to restore, because it won't work." Try it like this: psql famiglia < /mnt/redhat/home/postgres_postgres/famiglia.dump This assumes your dump is in SQL format, which pg_dump creates by default. If you used custom or tar format, you need to use 'pg_restore' instead. See the documentation for more info... -Doug
"Antonio G. - Geotronix" <nightnavigator@libero.it> writes: >> Hi. >> >> I'm trying to import some databases I had on redhat 7.2 (think it had >> postgressql-7.1.x series but not sure) to my new Debian woody >> installation (postgresql 7.2.1-2woody2). I created a database called >> 'famiglia' under the postgres user. Then, always by postgres user i >> did: >> >> gaia:/etc/postgresql$ pg_dump famiglia < >> /mnt/redhat/home/postgres_postgres \ >> /famiglia.dump >Ummm.... You don't use pg_dump to restore. > > >-Doug Hi. I'm sorry to bother you in private but I'm not recieving the ML posts for some reason and even though I've written to the ML owner he/she hasn't still repied for some reason. So I read via web and send via email client :-) In your reply you told me that I'm not using pg_dump to restore. But as you can see in my previus message I am using pg_dump :-) >> gaia:/etc/postgresql$ pg_dump famiglia < >> /mnt/redhat/home/postgres_postgres \ >> /famiglia.dump Bye
"Antonio G. - Geotronix" <nightnavigator@libero.it> writes: >> Hi. >> >> I'm trying to import some databases I had on redhat 7.2 (think it had >> postgressql-7.1.x series but not sure) to my new Debian woody >> installation (postgresql 7.2.1-2woody2). I created a database called >> 'famiglia' under the postgres user. Then, always by postgres user i >> did: >> >> gaia:/etc/postgresql$ pg_dump famiglia < >> /mnt/redhat/home/postgres_postgres \ >> /famiglia.dump >Ummm.... You don't use pg_dump to restore. > >-Doug Hi. I'm sorry to bother you in private but I'm not recieving the ML posts for some reason and even though I've written to the ML owner he/she hasn't still repied for some reason. So I read via web and send via email client :-) In your reply you told me that I'm not using pg_dump to restore. But as you can see in my previus message I am using pg_dump :-) >> gaia:/etc/postgresql$ pg_dump famiglia < >> /mnt/redhat/home/postgres_postgres \ >> /famiglia.dump Bye