Dumping postgres DBs into separate files

Question

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

Answer 1

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' 
postgres
template0
⋮
template1
test