Le 06/05/2010 17:56, Thom Brown a écrit : > On 6 May 2010 16:52, Scott Mead <scott.m...@enterprisedb.com> wrote: > >> >> On Thu, May 6, 2010 at 11:30 AM, Thom Brown <thombr...@gmail.com> wrote: >> >>> On 6 May 2010 16:15, Andy Colson <a...@squeakycode.net> wrote: >>> >>>> On 5/6/2010 2:57 AM, Jaume Calm wrote: >>>> >>>>> Hi! I was searching for a command like pg_dumpall but with the >>>>> difference that I don’t want a single file for all databases, i would >>>>> like to have a file for each one. >>>>> >>>>> I couldn’t fins such command, so the only option I see is to write a >>>>> shell script with a loop for all the DBs. The problem is that I’m unable >>>>> to find the way to obtain the DBs’ names in a shell script. Can someone >>>>> help me with this? >>>>> >>>>> Best regards and thank you all for your time. >>>>> >>>>> >>>> Depending on what version of PG you are on, try: >>>> >>>> psql -ltA >>>> >>>> a little read, cut, awk, perl, etc action and you should be good. >>>> >>>> -Andy >>>> >>>> >>> Aha, yes, I should really look at the psql options more. >>> >>> You could extend that to exclude templates and the postrgres database and >>> database attributes: >>> >>> psql -ltA | cut -d "|" -f 1 | grep -v "\( template0 \| template1 \| >>> postgres \| : \)" >>> >>> And using Scott's loop: >>> >>> for line in `psql -lt | cut -d "|" -f 1 | grep -v "\( template0 \| >>> template1 \| postgres \| : \)" | head -n -1 `; do pg_dump -f >>> /home/backups/`date +\%Y\%m\%d`/"$line".sql; done >>> >> >> Slightly: >> >> for line in `psql -t postgres -c "select datname from pg_database where >> datname not in ('template0','template1','postgres')" `; do pg_dump -f >> /home/backups/`date +\%Y\%m\%d`/"$line".sql ; done >> >> > > Yeah, that'll work better. It's certainly more legible. Could that > potentially choke on database names with spaces or weird characters in do > you reckon? >
Not if you put it between quotes. BTW, your pg_dump command misses a $line at the end to indicate the database to dump. And rather than checking the database name, I usually prefer to filter with datallowconn. This way: query="select datname from pg_database where datallowconn=true" for line in `psql -t -c "$query" postgres` do pg_dump -f /home/backups/`date +\%Y\%m\%d`/"$line".sql "$line" done It'll save template1 and postgres, but the script won't give me an error on template0. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general