469,648 Members | 1,568 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,648 developers. It's quick & easy.

pg_dump in cycle

Dear Sirs,

I want to dump all databases, but separately each database in its own
file, not all databases in one single file as pg_dumpall does.

How can I implement that ?

Cheers,
Ilia Chipitsine

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #1
6 2107
Use pg_dump instead of pg_dumpall

example:
pg_dump databaseName > databaseDumpFile

if u have many databases, you can make a script that dumps each database in
it's own file ....

----- Original Message -----
From: "Ilia Chipitsine" <il**@paramon.ru>
To: <pg***********@postgresql.org>
Sent: Thursday, September 16, 2004 8:08 AM
Subject: [GENERAL] pg_dump in cycle

Dear Sirs,

I want to dump all databases, but separately each database in its own
file, not all databases in one single file as pg_dumpall does.

How can I implement that ?

Cheers,
Ilia Chipitsine

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2
U can use :
select datname from pg_database;

in order to get the list of databses

HTH

Najib.

----- Original Message -----
From: "Ilia Chipitsine" <il**@paramon.ru>
To: "Najib Abi Fadel" <na*******@usj.edu.lb>
Sent: Thursday, September 16, 2004 10:41 AM
Subject: Re: [GENERAL] pg_dump in cycle

Use pg_dump instead of pg_dumpall

example:
pg_dump databaseName > databaseDumpFile

if u have many databases, you can make a script that dumps each database
in
sure, I have many databases.
how can I write such script without explicitly specifying database names ?
I do not want to modify that script after I have added database.
it's own file ....

----- Original Message -----
From: "Ilia Chipitsine" <il**@paramon.ru>
To: <pg***********@postgresql.org>
Sent: Thursday, September 16, 2004 8:08 AM
Subject: [GENERAL] pg_dump in cycle

Dear Sirs,

I want to dump all databases, but separately each database in its own
file, not all databases in one single file as pg_dumpall does.

How can I implement that ?

Cheers,
Ilia Chipitsine

---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3
yes, but how can I integrate that query with shell script (which will
perform actual dumping) ?

I would even say, "select datname from pg_database where not
datistemplate", becuase otherwise pg_dump will complain about template0

Cheers,
Ilia Chipitsine

U can use :
select datname from pg_database;

in order to get the list of databses

HTH

Najib.

----- Original Message -----
From: "Ilia Chipitsine" <il**@paramon.ru>
To: "Najib Abi Fadel" <na*******@usj.edu.lb>
Sent: Thursday, September 16, 2004 10:41 AM
Subject: Re: [GENERAL] pg_dump in cycle

Use pg_dump instead of pg_dumpall

example:
pg_dump databaseName > databaseDumpFile

if u have many databases, you can make a script that dumps each database in

sure, I have many databases.
how can I write such script without explicitly specifying database names ?
I do not want to modify that script after I have added database.
it's own file ....

----- Original Message -----
From: "Ilia Chipitsine" <il**@paramon.ru>
To: <pg***********@postgresql.org>
Sent: Thursday, September 16, 2004 8:08 AM
Subject: [GENERAL] pg_dump in cycle
Dear Sirs,

I want to dump all databases, but separately each database in its own
file, not all databases in one single file as pg_dumpall does.

How can I implement that ?

Cheers,
Ilia Chipitsine

---------------------------(end of

broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #4

Check out the "psql " command.
U can use :

psql -l

which outputs sometinhg like
List of databases
Name | Owner | Encoding
-----------------+-----------+-----------
dragon_devel | ptufenkji | UNICODE
dragon_devel_v2 | ptufenkji | UNICODE
dragon_joujou | ptufenkji | UNICODE
dragon_prod | ptufenkji | UNICODE
fgm | gnakhle | UNICODE
fgm_eval | ptufenkji | UNICODE
hotline | postgres | UNICODE
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
usj | ptufenkji | UNICODE
for the shell script and do some text filtering in order to retrieve the
databse names.

(Or may be u can do a connection to the database from the shell script i am
not sure: i don't have a big experience in shell scripting)

----- Original Message -----
From: "Ilia Chipitsine" <il**@paramon.ru>
To: "Najib Abi Fadel" <na*******@usj.edu.lb>
Cc: "generalpost" <pg***********@postgresql.org>
Sent: Thursday, September 16, 2004 11:01 AM
Subject: Re: [GENERAL] pg_dump in cycle

yes, but how can I integrate that query with shell script (which will
perform actual dumping) ?

I would even say, "select datname from pg_database where not
datistemplate", becuase otherwise pg_dump will complain about template0

Cheers,
Ilia Chipitsine

U can use :
select datname from pg_database;

in order to get the list of databses

HTH

Najib.

----- Original Message -----
From: "Ilia Chipitsine" <il**@paramon.ru>
To: "Najib Abi Fadel" <na*******@usj.edu.lb>
Sent: Thursday, September 16, 2004 10:41 AM
Subject: Re: [GENERAL] pg_dump in cycle

Use pg_dump instead of pg_dumpall

example:
pg_dump databaseName > databaseDumpFile

if u have many databases, you can make a script that dumps each database
in

sure, I have many databases.
how can I write such script without explicitly specifying database

names ? I do not want to modify that script after I have added database.

it's own file ....

----- Original Message -----
From: "Ilia Chipitsine" <il**@paramon.ru>
To: <pg***********@postgresql.org>
Sent: Thursday, September 16, 2004 8:08 AM
Subject: [GENERAL] pg_dump in cycle
> Dear Sirs,
>
> I want to dump all databases, but separately each database in its own
> file, not all databases in one single file as pg_dumpall does.
>
> How can I implement that ?
>
> Cheers,
> Ilia Chipitsine
>
> ---------------------------(end of

broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #5
This seems to be more interesting for shell scripting:

psql -d DatabaseName -c 'select datname from pg_database where not
datistemplate' ;

datname
-----------------
fgm_eval
hotline
usj
dragon_devel
dragon_joujou
dragon_devel_v2
dragon_prod
fgm
(8 rows)

Cheers.
Najib.

----- Original Message -----
From: "Najib Abi Fadel" <na*******@usj.edu.lb>
To: "Ilia Chipitsine" <il**@paramon.ru>
Cc: "generalpost" <pg***********@postgresql.org>
Sent: Thursday, September 16, 2004 12:08 PM
Subject: Re: [GENERAL] pg_dump in cycle


Check out the "psql " command.
U can use :

psql -l

which outputs sometinhg like
List of databases
Name | Owner | Encoding
-----------------+-----------+-----------
dragon_devel | ptufenkji | UNICODE
dragon_devel_v2 | ptufenkji | UNICODE
dragon_joujou | ptufenkji | UNICODE
dragon_prod | ptufenkji | UNICODE
fgm | gnakhle | UNICODE
fgm_eval | ptufenkji | UNICODE
hotline | postgres | UNICODE
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
usj | ptufenkji | UNICODE
for the shell script and do some text filtering in order to retrieve the
databse names.

(Or may be u can do a connection to the database from the shell script i am not sure: i don't have a big experience in shell scripting)

----- Original Message -----
From: "Ilia Chipitsine" <il**@paramon.ru>
To: "Najib Abi Fadel" <na*******@usj.edu.lb>
Cc: "generalpost" <pg***********@postgresql.org>
Sent: Thursday, September 16, 2004 11:01 AM
Subject: Re: [GENERAL] pg_dump in cycle

yes, but how can I integrate that query with shell script (which will
perform actual dumping) ?

I would even say, "select datname from pg_database where not
datistemplate", becuase otherwise pg_dump will complain about template0

Cheers,
Ilia Chipitsine

U can use :
select datname from pg_database;

in order to get the list of databses

HTH

Najib.

----- Original Message -----
From: "Ilia Chipitsine" <il**@paramon.ru>
To: "Najib Abi Fadel" <na*******@usj.edu.lb>
Sent: Thursday, September 16, 2004 10:41 AM
Subject: Re: [GENERAL] pg_dump in cycle
>> Use pg_dump instead of pg_dumpall
>>
>> example:
>> pg_dump databaseName > databaseDumpFile
>>
>> if u have many databases, you can make a script that dumps each database in
>
> sure, I have many databases.
> how can I write such script without explicitly specifying database names ?> I do not want to modify that script after I have added database.
>
>> it's own file ....
>>
>>
>>
>> ----- Original Message -----
>> From: "Ilia Chipitsine" <il**@paramon.ru>
>> To: <pg***********@postgresql.org>
>> Sent: Thursday, September 16, 2004 8:08 AM
>> Subject: [GENERAL] pg_dump in cycle
>>
>>
>>> Dear Sirs,
>>>
>>> I want to dump all databases, but separately each database in its own>>> file, not all databases in one single file as pg_dumpall does.
>>>
>>> How can I implement that ?
>>>
>>> Cheers,
>>> Ilia Chipitsine
>>>
>>> ---------------------------(end of
broadcast)---------------------------
>>> TIP 4: Don't 'kill -9' the postmaster
>>>
>>
>>
>


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #6
* Najib Abi Fadel:
| This seems to be more interesting for shell scripting:
|
| psql -d DatabaseName -c 'select datname from pg_database where not
| datistemplate' ;
|
| datname
| -----------------
| fgm_eval
| hotline
| usj
| dragon_devel
| dragon_joujou
| dragon_devel_v2
| dragon_prod
| fgm
| (8 rows)

$ psql -ltA | cut -d'|' -f1 | grep -v '^template' | xargs -i pg_dump -f {}.pg_dump {}
--
-- PostgreSQL database dump complete
--

--
-- PostgreSQL database dump complete
--

$ ls -l *.pg_dump
-rw-r--r-- 1 postgres users 493 2004-09-17 14:00 foo.pg_dump
-rw-r--r-- 1 postgres users 3326 2004-09-17 14:00 test.pg_dump

--
Lars Haugseth

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Sam | last post: by
9 posts views Thread by Alexander Cohen | last post: by
4 posts views Thread by Philip | last post: by
1 post views Thread by Ben-Nes Michael | last post: by
reply views Thread by David Suela Fernández | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.