Thread: pg_restore
Hi all, We are running postgres 8.03. Getting a dump is no problem on our 6 GB database. However, the restore is a killer, it takes 10 minutes for the dump pg_dump, but over 5 hours for a restore. Is there a way to 'turn off transactions' on the restore to speed things up? I've tried different restore commands, but I don't see anything on transactions logging. What else could speed it up? oracle and sybase can restore the same db in just 90 minutes. Does postgres just not know how to take advantage of the resources unless different users are involved? In other words to speed it up, should I script different users to each restore some of the tables to a database and then after all the tables have restored, restore keys and then indexes? Thanks for any help. ~DjK psql dbname < dump_file pg_restore -d test_restore_2 -Fc niehs_dump.backup
On Thu, Aug 11, 2005 at 15:29:22 -0400, D Kavan <bitsandbytes88@hotmail.com> wrote: > Hi all, > > We are running postgres 8.03. > > Getting a dump is no problem on our 6 GB database. However, the restore is > a killer, it takes 10 minutes for the dump pg_dump, but over 5 hours for a > restore. Is there a way to 'turn off transactions' on the restore to speed > things up? I've tried different restore commands, but I don't see > anything on transactions logging. What else could speed it up? oracle > and sybase can restore the same db in just 90 minutes. Does postgres > just not know how to take advantage of the resources unless different users > are involved? In other words to speed it up, should I script different > users to each restore some of the tables to a database and then after all > the tables have restored, restore keys and then indexes? Thanks for any > help. > > ~DjK > > psql dbname < dump_file > pg_restore -d test_restore_2 -Fc niehs_dump.backup Some things you can do are to turn off fsync for the restore and not restore foreign key contsraints and indexes until after the database has been loaded. This topic has been discussed before and you might find some more specific information in the archives.
Bruno Wolff III <bruno@wolff.to> writes:
>> psql dbname < dump_file
>> pg_restore -d test_restore_2 -Fc niehs_dump.backup
> Some things you can do are to turn off fsync for the restore and not
> restore foreign key contsraints and indexes until after the database
> has been loaded.
pg_dump/pg_restore should get the ordering considerations right already.
fsync off might help some but I'm not convinced of it. I'd look at the
value of maintenance_work_mem --- increasing that to maybe a few hundred
Mb would help the speed of index builds and foreign key checks.
But really what you ought to do first is find out where it's spending
its time. Can you run the restore with query logging and duration
logging enabled?
regards, tom lane
Thanks! We increased work memory from 16 MB to 32 MB and maintenance work memory from 256 MB to 768 MB. That made a huge difference. We did set true the log.statement and duration, but where is the file those are logging too? That did the trick. 30 minutes only for a restrore instead of 5 + hours. I did notice that the size is 4 GB instead of 5.6 GB. Is something else going on? We tried a pg_restore with -Fc once and only got 4 GB, but the next time with a standard pg_dump command and psql newdb < dumpfile command, we also had 4 GB. ~DjK
"D Kavan" <bitsandbytes88@hotmail.com> writes:
> That did the trick. 30 minutes only for a restrore instead of 5 + hours.
Good.
> I did notice that the size is 4 GB instead of 5.6 GB. Is something else
> going on?
Kinda sounds like you weren't vacuuming often enough in the old
installation, leading to table or index bloat ...
regards, tom lane
Hello all: I am working with the new log redirection and rotation possibilities in postgres 8.0.3. Here is my question. I'm using redirect_stderr=true in my conf file. This works as I expect -- but I still seem to see some log messages trickle through to the console. I assume these are appearing on stdout? Formerly, I would always redirect stdout and stderr to the same logfile. I tried that here, starting postmaster with 1>&2 (instead of 2>&1 as I used to) but it doesn't seem to help. Am I barking up the wrong tree? -- sgl -------- Steve Lane Vice President Soliant Consulting, Inc. (610) 788-2124 (V) (847) 890-6029 (F) slane@soliantconsulting.com
On Fri, Aug 12, 2005 at 04:23:01PM -0400, Steve Lane wrote: > Hello all: > > I am working with the new log redirection and rotation possibilities in > postgres 8.0.3. Here is my question. > > I'm using redirect_stderr=true in my conf file. This works as I expect -- > but I still seem to see some log messages trickle through to the console. I > assume these are appearing on stdout? What messages? -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) Oh, oh, las chicas galacianas, lo harán por las perlas, ¡Y las de Arrakis por el agua! Pero si buscas damas Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)
I do a full vacuum and analyze every night, but I'm getting this error. chived transaction log file "000000010000001200000091" < % %2005-08-12 06:13:49 EDT>LOG: archived transaction log file "000000010000001200000092" < % %2005-08-12 06:13:57 EDT>LOG: archived transaction log file "000000010000001200000093" < % %2005-08-12 06:17:51 EDT>LOG: checkpoints are occurring too frequently (16 seconds apart) < % %2005-08-12 06:17:51 EDT>HINT: Consider increasing the configuration parameter "checkpoint_segments". <postgres %niehs_dev %2005-08-12 06:17:52 EDT>LOG: duration: 2426771.793 ms statement: VACUUM FULL;
D Kavan wrote: > I do a full vacuum and analyze every night, but I'm getting this error. Those aren't errors. They are letting you know that you need to increase your checkpoint_segments. > > chived transaction log file "000000010000001200000091" > < % %2005-08-12 06:13:49 EDT>LOG: archived transaction log file > "000000010000001200000092" > < % %2005-08-12 06:13:57 EDT>LOG: archived transaction log file > "000000010000001200000093" > < % %2005-08-12 06:17:51 EDT>LOG: checkpoints are occurring too > frequently (16 seconds apart) > < % %2005-08-12 06:17:51 EDT>HINT: Consider increasing the > configuration parameter "checkpoint_segments". > <postgres %niehs_dev %2005-08-12 06:17:52 EDT>LOG: duration: > 2426771.793 ms statement: VACUUM FULL; > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
Here's an example: 2005-08-12 15:12:43 CDT 12465 LOG: XX000: could not create IPv6 socket: Address family not supported by protocol 2005-08-12 15:12:43 CDT 12465 LOCATION: StreamServerPort, pqcomm.c:337 These semm to be appearing in the written logs as well. -- steve > From: Alvaro Herrera <alvherre@alvh.no-ip.org> > Date: Fri, 12 Aug 2005 16:39:35 -0400 > To: Steve Lane <slane@soliantconsulting.com> > Cc: <pgsql-admin@postgresql.org> > Subject: Re: [ADMIN] Log stdout in PG 8? > > On Fri, Aug 12, 2005 at 04:23:01PM -0400, Steve Lane wrote: >> Hello all: >> >> I am working with the new log redirection and rotation possibilities in >> postgres 8.0.3. Here is my question. >> >> I'm using redirect_stderr=true in my conf file. This works as I expect -- >> but I still seem to see some log messages trickle through to the console. I >> assume these are appearing on stdout? > > What messages? > > -- > Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) > Oh, oh, las chicas galacianas, lo harán por las perlas, > ¡Y las de Arrakis por el agua! Pero si buscas damas > Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)
Steve Lane <slane@soliantconsulting.com> writes:
> Here's an example:
> 2005-08-12 15:12:43 CDT 12465 LOG: XX000: could not create IPv6 socket:
> Address family not supported by protocol
> 2005-08-12 15:12:43 CDT 12465 LOCATION: StreamServerPort, pqcomm.c:337
> These semm to be appearing in the written logs as well.
There is not anything in the backend that would send that to stdout as
opposed to stderr.
What do you mean by "written logs" exactly?
What have you got log_destination set to?
regards, tom lane