Thread: pg_dump in stand alone backend

pg_dump in stand alone backend

From
Ulrich Wisser
Date:
Hi,

I would like to stop the postmaster every night and run

vacuum
pg_dump
reindex

in the stand alone backend.

Vacuum and reindex seem to be quite easy, as I can setup a small script
with both commands. But what about pg_dump. That seems "somewhat" more
complex.

TIA

Ulrich


Re: pg_dump in stand alone backend

From
Daniel Martini
Date:
Hi,

Citing Ulrich Wisser <ulrich.wisser@relevanttraffic.se>:
> I would like to stop the postmaster every night and run
>
> vacuum
> pg_dump
> reindex
>
> in the stand alone backend.
>
> Vacuum and reindex seem to be quite easy, as I can setup a small script
> with both commands. But what about pg_dump. That seems "somewhat" more
> complex.

What exactly is your problem about putting pg_dump in a (shell)script as
well?
In the simplest case you could use something like:

#!/bin/sh
psql --command vacuum your_database
cd /somewhere/with/write/access
pg_dump your_database > dump_`date %Y%m%d%H%M%S`.bak
psql --command 'reindex whatever_you_want_to_reindex' your_database

which will vacuum, dump to a file named dump_timewhendumpoccured.bak and
reindex whatever_you_want_to_reindex. All kinds of stuff could be added
(like mailing command output to you, loading the backup up to another
machine for storage etc. etc.), but above script does basically what
you were asking for.

Regards,
Daniel

Re: pg_dump in stand alone backend

From
Ulrich Wisser
Date:
Daniel Martini wrote:

> Hi,
>
> Citing Ulrich Wisser <ulrich.wisser@relevanttraffic.se>:
>
>>I would like to stop the postmaster every night and run
>>
>>vacuum
>>pg_dump
>>reindex
>>
>>in the stand alone backend.
>>
>>Vacuum and reindex seem to be quite easy, as I can setup a small script
>>with both commands. But what about pg_dump. That seems "somewhat" more
>>complex.
>
>
> What exactly is your problem about putting pg_dump in a (shell)script as
> well?

psql will not work without postmaster running. But if I let postmaster
run some other tools will connect to it and I can't get exclusive locks
on all tables for "vacuum full" and "reindex". With vacuum and reindex
I can do

#!/bin/bash
postgres -d /var/lib/pgsql/data -U xxx -O -P mydb < SQL
REINDEX DATABASE mydb
REINDEX TABLE t1 FORCE
REINDEX TABLE t2
...
REINDEX TABLE tn
VACUUM FULL VERBOSE ANALYZE
<<SQL

which will speed up my database on a daily basis at least by factor 5.
For security reasons I would also like to take a backup and I guess it
will be much faster in the stand alone backend.

Ulrich



Re: pg_dump in stand alone backend

From
Gaetano Mendola
Date:
Ulrich Wisser wrote:

> Hi,
>
> I would like to stop the postmaster every night and run
>
> vacuum
> pg_dump
> reindex
>
> in the stand alone backend.
>
> Vacuum and reindex seem to be quite easy, as I can setup a small script
> with both commands. But what about pg_dump. That seems "somewhat" more
> complex.

Explain what exactly you are trying to do, why do you have to stop
the postmaster ? If you request is due only to forbid the access then
you can replace the pg_hba.conf with a "void" one and replace it again
at the end of operations.

BTW vacuum, pg_dump, reindex are operations that can be performed
with the server up and running.

Regards
Gaetano Mendola




Re: pg_dump in stand alone backend

From
Ulrich Wisser
Date:
Hi,

>> I would like to stop the postmaster every night and run
>>
>> vacuum
>> pg_dump
>> reindex
>>
>> in the stand alone backend.
>>
>> Vacuum and reindex seem to be quite easy, as I can setup a small
>> script with both commands. But what about pg_dump. That seems
>> "somewhat" more complex.
>
>
> Explain what exactly you are trying to do, why do you have to stop
> the postmaster ? If you request is due only to forbid the access then
> you can replace the pg_hba.conf with a "void" one and replace it again
> at the end of operations.
>
> BTW vacuum, pg_dump, reindex are operations that can be performed
> with the server up and running.

on my database server I have a lot of scripts running. Some of them are
not under my control and some of these don't really behave nice. So when
my "maintainance period" starts they will still be running for hours and
  take exclusive locks on 100,000+ rows in various tables. Which means
neither "vacuum full" nor "reindex" can get locks on these tables. Which
stalls these calls and delays them to a time when my well behaving but
heavy duty scripts start running again. Now these scripts will be
delayed be "vacuum" or "reindex" and when I get to my desk the next
morning the whole system is in overload.

I need to to disconnect any other users and do "vacuum full verbose
analyze" "reindex database" and reindex all tables. And for these I will
stop the postmaster and run a stand alone backend.

I figured that doing a nightly backup would be a good idea and running
it in stand alone mode will speed up the process drastically.

Ulrich


Re: pg_dump in stand alone backend

From
Richard Huxton
Date:
Ulrich Wisser wrote:
>>
>> Explain what exactly you are trying to do, why do you have to stop
>> the postmaster ? If you request is due only to forbid the access then
>> you can replace the pg_hba.conf with a "void" one and replace it again
>> at the end of operations.
[snip]
> I need to to disconnect any other users and do "vacuum full verbose
> analyze" "reindex database" and reindex all tables. And for these I will
> stop the postmaster and run a stand alone backend.
>
> I figured that doing a nightly backup would be a good idea and running
> it in stand alone mode will speed up the process drastically.

Don't think it makes much difference, assuming you're the only one
connected. I'd follow Gaetano's idea and have separate
postgresql.conf/pg_hba.conf files.
  1. stop PG
  2. swap conf files
  3. start PG
  4. maintenance
  5. stop PG
  6. swap conf files back
  7. start PG

A separate postgresql.conf lets you have different sort_mem values etc.
for your nightly maintenance too.
--
   Richard Huxton
   Archonet Ltd

Re: pg_dump in stand alone backend

From
Daniel Martini
Date:
Hi,

Citing Ulrich Wisser <ulrich.wisser@relevanttraffic.se>:
> on my database server I have a lot of scripts running. Some of them are
> not under my control and some of these don't really behave nice. So when
> my "maintainance period" starts they will still be running for hours and
>   take exclusive locks on 100,000+ rows in various tables. Which means
> neither "vacuum full" nor "reindex" can get locks on these tables. Which
> stalls these calls and delays them to a time when my well behaving but
> heavy duty scripts start running again. Now these scripts will be
> delayed be "vacuum" or "reindex" and when I get to my desk the next
> morning the whole system is in overload.
>
> I need to to disconnect any other users and do "vacuum full verbose
> analyze" "reindex database" and reindex all tables. And for these I will
> stop the postmaster and run a stand alone backend.

How about stopping the postmaster, running vacuum and reindex in the
standalone backend, then starting the postmaster with a different
pg_hba.conf and optionally setting a few options from postgresql.conf
from the commandline (e.g. MAX_CONNECTIONS,
SUPERUSER_RESERVED_CONNECTIONS) to limit access to the server to the
user doing the dump and then doing the dump. When it is finished, you
could restart the postmaster with the proper runtime environment in
place. Would this work?

> I figured that doing a nightly backup would be a good idea and running
> it in stand alone mode will speed up the process drastically.

It seems, we can't try if it is really drastically faster than just
starting the postmaster with restricted access and then doing the dump.
Another option would be to look at the source to determine what pg_dump
actually does and write a sql-script to come as close as possible to that
and postprocess the output of feeding this to the standalone backend to
produce again sql which could be loaded by psql.
Probably a lot of work, but perhaps doable if you only care about the
actual data and not about triggers, functions, access permissions etc. etc.

Regards,
Daniel

Re: pg_dump in stand alone backend

From
Tom Lane
Date:
Ulrich Wisser <ulrich.wisser@relevanttraffic.se> writes:
> I need to to disconnect any other users and do "vacuum full verbose
> analyze" "reindex database" and reindex all tables. And for these I will
> stop the postmaster and run a stand alone backend.

I think the real problem here is stone-age maintenance procedures ;-)
You shouldn't need to do vacuum full on a regular basis, and you
shouldn't need to do reindexing on a regular basis either.  Update
to 7.4, if you aren't using it already, and replace these procedures
by plain vacuums run often enough to keep the DB from bloating (a look
at your FSM parameters would be advisable too).

> I figured that doing a nightly backup would be a good idea and running
> it in stand alone mode will speed up the process drastically.

No it won't.

            regards, tom lane