I inherited a postgres DB (which I have very little experience with) and trying to figure out a way to dump/back it up. I've been reading docs about it and can get as far as dumping the DBs into separate files but wondering why I can't dump template0. As I understand this is a sort of "defaults" template which seems important to hang onto if modified. Why isn't this working?

test# export PGPASSWORD="xxxxxxx"; for database in `psql --username=postgres --command='\list' -h localhost | grep '^ [a-zA-Z0-9]' | awk '{print $1};'`; do pg_dump -U postgres ${database} > ${database}.sql; done                    

pg_dump: [archiver (db)] connection to database "template0" failed: FATAL:  database "template0" is not currently accepting connections

template0 is created when installing PostgreSQL and should not contain any local modifications; there shouldn't be any need to back it up. Local modifications go in to template1 instead. (See https://www.postgresql.org/docs/current/static/manage-ag-templatedbs.html).

It's failing because template0 doesn't allow connections (which is the default, to protect it from accidentally getting local modifications):

postgres=# select datname, datallowconn from pg_database where datname = 'template0';
  datname  | datallowconn 
 template0 | f
(1 row)

PS: PostgreSQL can be set up to use non-password authentication, which would save you from having to put a password in your script. At least when running against a local instance.

PPS: Also, you can save yourself that grep/awk mess with a select from pg_database:

$ psql postgres -Atc 'select datname from pg_database' 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.