Обсуждение: Fwd: restoring table

Dear Team,

Postgresql Version:8.4

I could not  restore a table (4.1GB) in postgresql database server.

This is  the command I  used to  take table dump,

pg_dump   databasename  -t tablename  --format plain --data-only > ~/backup.sql

Restore command

 psql   -U  username  -d databasename -f  backup.sql

I  am  getting  below error,

2014-01-01 17:43:53 IST LOG:  autovacuum launcher started
2014-01-01 18:09:41 IST ERROR:  canceling autovacuum task
2014-01-01 18:09:41 IST CONTEXT:  automatic vacuum of table "tablename"
2014-01-01 18:12:31 IST LOG:  server process (PID 60667) was terminated by signal 9: Killed
2014-01-01 18:12:31 IST LOG:  terminating any other active server processes
2014-01-01 18:12:31 IST WARNING:  terminating connection because of crash of another server process
2014-01-01 18:12:31 IST DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2014-01-01 18:12:31 IST HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2014-01-01 18:12:31 IST LOG:  all server processes terminated; reinitializing
2014-01-01 18:12:32 IST LOG:  database system was interrupted; last known up at 2014-01-01 18:12:04 IST
2014-01-01 18:12:32 IST LOG:  database system was not properly shut down; automatic recovery in progress
2014-01-01 18:12:32 IST LOG:  redo starts at 5/2800EE48
2014-01-01 18:12:34 IST LOG:  unexpected pageaddr 5/13240000 in log file 5, segment 53, offset 2359296
2014-01-01 18:12:34 IST LOG:  redo done at 5/3523FFA8
2014-01-01 18:13:01 IST LOG:  database system is ready to accept connections
2014-01-01 18:13:01 IST LOG:  autovacuum launcher started

Sharil

--
Sharil

sharilalipv <sharilalipv@gmail.com> writes:
> I could not  restore a table (4.1GB) in postgresql database server.

> 2014-01-01 18:12:31 IST LOG:  server process (PID 60667) was terminated by
> signal 9: Killed

OOM killer at work, evidently.  You should consider adjusting your kernel
settings to prevent memory overcommit.  However, for this particular case
that might not do much beyond allowing a more graceful failure.  I'm going
to guess that the reason the backend process is eating memory is that
there's a foreign key constraint on the table, or some other reason to
fire AFTER triggers for it, so that the trigger event list is getting big.
You would be best off dropping the foreign key(s) and then recreating them
after you've loaded the data --- this will likely be faster overall, as
well as less prone to memory bloat.

This advice as well as other useful info can be found at
http://www.postgresql.org/docs/9.3/static/populate.html

                        regards, tom lane