pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedrestoring table
6+ messages / 4 participants
[nested] [flat]
* restoring table
@ 2014-01-02 06:50 sharilalipv <sharil@posibolt.com>
0 siblings, 1 reply; 6+ messages in thread
From: sharilalipv @ 2014-01-02 06:50 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Fwd: restoring table
@ 2014-01-02 06:52 sharilalipv <sharilalipv@gmail.com>
parent: sharilalipv <sharil@posibolt.com>
0 siblings, 2 replies; 6+ messages in thread
From: sharilalipv @ 2014-01-02 06:52 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Fwd: restoring table
@ 2014-01-02 07:06 Doom.zhou <zzepaigh@gmail.com>
parent: sharilalipv <sharilalipv@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Doom.zhou @ 2014-01-02 07:06 UTC (permalink / raw)
To: sharilalipv <sharilalipv@gmail.com>; +Cc: pgsql-admin
your server resource & your configure file ?
On Thu, Jan 2, 2014 at 2:52 PM, sharilalipv <sharilalipv@gmail.com> wrote:
> 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Fwd: restoring table
@ 2014-01-02 07:35 sharilalipv <sharilalipv@gmail.com>
parent: Doom.zhou <zzepaigh@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: sharilalipv @ 2014-01-02 07:35 UTC (permalink / raw)
To: Doom.zhou <zzepaigh@gmail.com>; +Cc: pgsql-admin
4 GB RAM and Quad core xeon processor .
I made following changes in default postgresql.conf file.
shared_buffers = 768MB
work_mem = 5MB
checkpoint_segments = 20
effective_cache_size = 1536MB
Sharil
On Thu, Jan 2, 2014 at 12:36 PM, Doom.zhou <zzepaigh@gmail.com> wrote:
> your server resource & your configure file ?
>
>
> On Thu, Jan 2, 2014 at 2:52 PM, sharilalipv <sharilalipv@gmail.com> wrote:
>
>> 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
>>
>
>
--
Sharil
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Fwd: restoring table
@ 2014-01-02 14:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: sharilalipv <sharilalipv@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2014-01-02 14:22 UTC (permalink / raw)
To: sharilalipv <sharilalipv@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Fwd: restoring table
@ 2014-01-03 06:13 sharilalipv <sharilalipv@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: sharilalipv @ 2014-01-03 06:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Dear Tom Lane,
Thanks a lot.I can restore table after drpping foreign key(s).
Sharil
On Thu, Jan 2, 2014 at 7:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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
>
--
Sharil
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2014-01-03 06:13 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-01-02 06:50 restoring table sharilalipv <sharil@posibolt.com>
2014-01-02 06:52 ` sharilalipv <sharilalipv@gmail.com>
2014-01-02 07:06 ` Doom.zhou <zzepaigh@gmail.com>
2014-01-02 07:35 ` sharilalipv <sharilalipv@gmail.com>
2014-01-02 14:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-01-03 06:13 ` sharilalipv <sharilalipv@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox