Postgres Pro
Downloads
Home   >   mailing lists

Thread: Database names and pg_dump

Database names and pg_dump

From
David Rickard
Date:
We are planning to migrate from Oracle to PostGreSQL, and we would like to replicate our database maintenance procedures/scripts as closely as possible;
Our Oracle backup scripts extract the names of all active SIDs from the /etc/oratab file and perform a backup export for each SID;
Is there an equivalent file or utility that will provide the name of each database within a PostGreSQL cluster?
I know you can extract an entire cluster via pg_dumpall, but we would like to stick with the individual db backups--for file-size issues and restore efficiency.

--

David Rickard
Software Engineer
The GTS Companies
A TechBooks Company

----------------------------------------------------------------------------------
The GTS Companies:
GTS Publishing Services, GTS Graphics, GTS Innova:
Your Single-Source Solution!
Los Angeles CA  *  York, PA  *  Boston MA  *  New Delhi, India
----------------------------------------------------------------------------------


David.Rickard@GTSCompanies.com
Visit us on the World Wide Web
http://www.gtscompanies.com
5650 Jillson St., Los Angeles, CA 90040
(323) 888-8889 x331
(323) 888-1849 [fax]

Re: Database names and pg_dump

From
Bruno Wolff III
Date:
On Tue, Aug 26, 2003 at 14:38:15 -0700,
  David Rickard <David.Rickard@GTScompanies.com> wrote:
> We are planning to migrate from Oracle to PostGreSQL, and we would like to
> replicate our database maintenance procedures/scripts as closely as
> possible;
> Our Oracle backup scripts extract the names of all active SIDs from the
> /etc/oratab file and perform a backup export for each SID;
> Is there an equivalent file or utility that will provide the name of each
> database within a PostGreSQL cluster?
> I know you can extract an entire cluster via pg_dumpall, but we would like
> to stick with the individual db backups--for file-size issues and restore
> efficiency.

pg_dump can be used to dump one database at a time.

psql -l can be used in a script to get a list of database names.

Re: Database names and pg_dump

From
Tomka Gergely
Date:
2003-08-26 ragyogó napján David Rickard ezt üzente:

> We are planning to migrate from Oracle to PostGreSQL, and we would like to
> replicate our database maintenance procedures/scripts as closely as possible;
> Our Oracle backup scripts extract the names of all active SIDs from the
> /etc/oratab file and perform a backup export for each SID;
> Is there an equivalent file or utility that will provide the name of each
> database within a PostGreSQL cluster?
> I know you can extract an entire cluster via pg_dumpall, but we would like
> to stick with the individual db backups--for file-size issues and restore
> efficiency.

Ehm, psql -l ?

--
Tomka Gergely
"S most - vajon barbárok nélkül mi lesz velünk?
Ők mégiscsak megoldás voltak valahogy..."

Re: Database names and pg_dump

From
Ron Johnson
Date:
On Tue, 2003-08-26 at 16:38, David Rickard wrote:
> We are planning to migrate from Oracle to PostGreSQL, and we would
> like to replicate our database maintenance procedures/scripts as
> closely as possible;
> Our Oracle backup scripts extract the names of all active SIDs from
> the /etc/oratab file and perform a backup export for each SID;
> Is there an equivalent file or utility that will provide the name of
> each database within a PostGreSQL cluster?
> I know you can extract an entire cluster via pg_dumpall, but we would
> like to stick with the individual db backups--for file-size issues and
> restore efficiency.

http://archives.postgresql.org/pgsql-interfaces/2003-08/msg00058.php

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

PETA - People Eating Tasty Animals

Re: Database names and pg_dump

From
Tom Lane
Date:
David Rickard <David.Rickard@GTScompanies.com> writes:
> Is there an equivalent file or utility that will provide the name of each
> database within a PostGreSQL cluster?

SELECT datname FROM pg_database;

            regards, tom lane

Re: Database names and pg_dump

From
Bruno LEVEQUE
Date:
You can do a "psql -l". With this you can see all databases you have
After this you can do a loop and a pg_dump for each one.

David Rickard wrote:

> We are planning to migrate from Oracle to PostGreSQL, and we would
> like to replicate our database maintenance procedures/scripts as
> closely as possible;
> Our Oracle backup scripts extract the names of all active SIDs from
> the /etc/oratab file and perform a backup export for each SID;
> Is there an equivalent file or utility that will provide the name of
> each database within a PostGreSQL cluster?
> I know you can extract an entire cluster via pg_dumpall, but we would
> like to stick with the individual db backups--for file-size issues and
> restore efficiency.
>
>
> --
>
> David Rickard
> Software Engineer
> The GTS Companies
> A TechBooks Company
>
> ----------------------------------------------------------------------------------
> The GTS Companies:
> GTS Publishing Services, GTS Graphics, GTS Innova:
> Your Single-Source Solution!
> Los Angeles CA  *  York, PA  *  Boston MA  *  New Delhi, India
> ----------------------------------------------------------------------------------
>
>
> David.Rickard@GTSCompanies.com
> Visit us on the World Wide Web
> http://www.gtscompanies.com
> 5650 Jillson St., Los Angeles, CA 90040
> (323) 888-8889 x331
> (323) 888-1849 [fax]
>
>

--
Bruno LEVEQUE
System Engineer
SARL NET6D

Re: Database names and pg_dump

From
Bruce Momjian
Date:
Should this be an FAQ?

---------------------------------------------------------------------------

Bruno LEVEQUE wrote:
> You can do a "psql -l". With this you can see all databases you have
> After this you can do a loop and a pg_dump for each one.
>
> David Rickard wrote:
>
> > We are planning to migrate from Oracle to PostGreSQL, and we would
> > like to replicate our database maintenance procedures/scripts as
> > closely as possible;
> > Our Oracle backup scripts extract the names of all active SIDs from
> > the /etc/oratab file and perform a backup export for each SID;
> > Is there an equivalent file or utility that will provide the name of
> > each database within a PostGreSQL cluster?
> > I know you can extract an entire cluster via pg_dumpall, but we would
> > like to stick with the individual db backups--for file-size issues and
> > restore efficiency.
> >
> >
> > --
> >
> > David Rickard
> > Software Engineer
> > The GTS Companies
> > A TechBooks Company
> >
> > ----------------------------------------------------------------------------------
> > The GTS Companies:
> > GTS Publishing Services, GTS Graphics, GTS Innova:
> > Your Single-Source Solution!
> > Los Angeles CA  *  York, PA  *  Boston MA  *  New Delhi, India
> > ----------------------------------------------------------------------------------
> >
> >
> > David.Rickard@GTSCompanies.com
> > Visit us on the World Wide Web
> > http://www.gtscompanies.com
> > 5650 Jillson St., Los Angeles, CA 90040
> > (323) 888-8889 x331
> > (323) 888-1849 [fax]
> >
> >
>
> --
> Bruno LEVEQUE
> System Engineer
> SARL NET6D
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Database names and pg_dump

From
Bruce Momjian
Date:
Bruce Momjian wrote:
>
> Should this be an FAQ?

I now see this is already in the FAQ, items 4.7 and 4.3.  Do we need
more?

---------------------------------------------------------------------------

>
> ---------------------------------------------------------------------------
>
> Bruno LEVEQUE wrote:
> > You can do a "psql -l". With this you can see all databases you have
> > After this you can do a loop and a pg_dump for each one.
> >
> > David Rickard wrote:
> >
> > > We are planning to migrate from Oracle to PostGreSQL, and we would
> > > like to replicate our database maintenance procedures/scripts as
> > > closely as possible;
> > > Our Oracle backup scripts extract the names of all active SIDs from
> > > the /etc/oratab file and perform a backup export for each SID;
> > > Is there an equivalent file or utility that will provide the name of
> > > each database within a PostGreSQL cluster?
> > > I know you can extract an entire cluster via pg_dumpall, but we would
> > > like to stick with the individual db backups--for file-size issues and
> > > restore efficiency.
> > >
> > >
> > > --
> > >
> > > David Rickard
> > > Software Engineer
> > > The GTS Companies
> > > A TechBooks Company
> > >
> > > ----------------------------------------------------------------------------------
> > > The GTS Companies:
> > > GTS Publishing Services, GTS Graphics, GTS Innova:
> > > Your Single-Source Solution!
> > > Los Angeles CA  *  York, PA  *  Boston MA  *  New Delhi, India
> > > ----------------------------------------------------------------------------------
> > >
> > >
> > > David.Rickard@GTSCompanies.com
> > > Visit us on the World Wide Web
> > > http://www.gtscompanies.com
> > > 5650 Jillson St., Los Angeles, CA 90040
> > > (323) 888-8889 x331
> > > (323) 888-1849 [fax]
> > >
> > >
> >
> > --
> > Bruno LEVEQUE
> > System Engineer
> > SARL NET6D
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if your
> >       joining column's datatypes do not match
> >
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073