Thread: Dump all in several files
Hi, On the PostgreSQL 6.5 server I use this shell script (see below) for the backup all of the database in several files, one file per database. With version 7.3 of PostgreSQL (and the following) the data are not recorded any more in a repertory with the name of the database, but with the OID of the basebase. Do you know how I can adapt this script? If a developer of pg_dumpall reads this post, is it possible to add to the command pg_dumpall an option to record one database per file? Regards. #! /bin/bash # for p in $(find /var/lib/postgres/data/base/ -type d -print -mindepth 1 -maxdepth 1 ) do base=`basename "$p"` pg_dump -d -f /var/backup/postgresql/dump_$base $base done -- ============================================== | FREDERIC MASSOT | | http://www.juliana-multimedia.com | | mailto:frederic@juliana-multimedia.com | ===========================Debian=GNU/Linux===
On Tue, 15 Mar 2005 16:43:01 +0100, Frederic Massot <frederic@juliana-multimedia.com> wrote: > Hi, > > On the PostgreSQL 6.5 server I use this shell script (see below) for the > backup all of the database in several files, one file per database. > > With version 7.3 of PostgreSQL (and the following) the data are not > recorded any more in a repertory with the name of the database, but with > the OID of the basebase. > > Do you know how I can adapt this script? > > If a developer of pg_dumpall reads this post, is it possible to add to > the command pg_dumpall an option to record one database per file? > > Regards. > > #! /bin/bash > # > for p in $(find /var/lib/postgres/data/base/ -type d -print -mindepth 1 > -maxdepth 1 ) > do > base=`basename "$p"` > pg_dump -d -f /var/backup/postgresql/dump_$base $base > done Maybe i'm just not following you, but why can't you just do: pg_dump $DBNAME >$DB_NAME where $DB_NAME is the name of each database on the box? -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand http://netllama.linux-sxs.org
Lonni J Friedman wrote: > On Tue, 15 Mar 2005 16:43:01 +0100, Frederic Massot > <frederic@juliana-multimedia.com> wrote: > >>Hi, >> >>On the PostgreSQL 6.5 server I use this shell script (see below) for the >>backup all of the database in several files, one file per database. >> [...] > > Maybe i'm just not following you, but why can't you just do: > pg_dump $DBNAME >$DB_NAME > > where $DB_NAME is the name of each database on the box? > This script is called in a crontab the every day at 6 o'clock in the morning. -- ============================================== | FREDERIC MASSOT | | http://www.juliana-multimedia.com | | mailto:frederic@juliana-multimedia.com | ===========================Debian=GNU/Linux===
On Tue, 15 Mar 2005 17:01:39 +0100, Frederic Massot <frederic@juliana-multimedia.com> wrote: > Lonni J Friedman wrote: > > On Tue, 15 Mar 2005 16:43:01 +0100, Frederic Massot > > <frederic@juliana-multimedia.com> wrote: > > > >>Hi, > >> > >>On the PostgreSQL 6.5 server I use this shell script (see below) for the > >>backup all of the database in several files, one file per database. > >> > [...] > > > > Maybe i'm just not following you, but why can't you just do: > > pg_dump $DBNAME >$DB_NAME > > > > where $DB_NAME is the name of each database on the box? > > > > This script is called in a crontab the every day at 6 o'clock in the > morning. i have a /etc/backup/data.pgsql where i list all databases i want to dump, then a script in /etc/cron.daily reads this file and call pg_dump: pg_dump -U postgres $1>$1.dump.sql javier
On Tue, Mar 15, 2005 at 05:01:39PM +0100, Frederic Massot wrote: > Lonni J Friedman wrote: > >On Tue, 15 Mar 2005 16:43:01 +0100, Frederic Massot > ><frederic@juliana-multimedia.com> wrote: > > > >>Hi, > >> > >>On the PostgreSQL 6.5 server I use this shell script (see below) for the > >>backup all of the database in several files, one file per database. > > > >Maybe i'm just not following you, but why can't you just do: > >pg_dump $DBNAME >$DB_NAME > > > >where $DB_NAME is the name of each database on the box? > > > > This script is called in a crontab the every day at 6 o'clock in the > morning. You can obtain the list of databases for scripting with psql -tlA | cut -d\| -f1 -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Si quieres ser creativo, aprende el arte de perder el tiempo"
On Tue, 15 Mar 2005 12:15:39 -0400, Alvaro Herrera
<alvherre@dcc.uchile.cl> wrote:
> On Tue, Mar 15, 2005 at 05:01:39PM +0100, Frederic Massot wrote:
> > Lonni J Friedman wrote:
> > >On Tue, 15 Mar 2005 16:43:01 +0100, Frederic Massot
> > ><frederic@juliana-multimedia.com> wrote:
> > >
> > >>Hi,
> > >>
> > >>On the PostgreSQL 6.5 server I use this shell script (see below) for the
> > >>backup all of the database in several files, one file per database.
> > >
> > >Maybe i'm just not following you, but why can't you just do:
> > >pg_dump $DBNAME >$DB_NAME
> > >
> > >where $DB_NAME is the name of each database on the box?
> > >
> >
> > This script is called in a crontab the every day at 6 o'clock in the
> > morning.
>
> You can obtain the list of databases for scripting with
>
> psql -tlA | cut -d\| -f1
>
i didn't know that. very nice. try:
psql -Upostgres -tlA |cut -d\| -f1|xargs -i pg_dump -Upostgres -f
'{}'.dump.sql '{}'
javier
Alvaro Herrera wrote: > On Tue, Mar 15, 2005 at 05:01:39PM +0100, Frederic Massot wrote: > [...] > > You can obtain the list of databases for scripting with > > psql -tlA | cut -d\| -f1 > Great !!! :o) Thank you. -- ============================================== | FREDERIC MASSOT | | http://www.juliana-multimedia.com | | mailto:frederic@juliana-multimedia.com | ===========================Debian=GNU/Linux===