According to the postgresql 8 manual, before starting a base backup (i.e. copying the files out of the data directory), you need to issue a command like this:

psql -d DATABASENAME -c "SELECT pg_start_backup('label');

But, since the postgresql files in the data directory contain ALL databases held by postgresql, what is the point of specifying DATABASENAME? (eg: mydb). Is it equally effecting to just use template0 as the databasename?

  • Have you tried to use psql without providing DB name? Usually all RDBMS backup single databases instead of the whole server. – McNets Feb 11 '18 at 14:49
  • McNets: ProgreSQL seems to be different (unless a pgsql explains otherwise) – TSG Feb 11 '18 at 20:09

Because you must connect to a specific DB in all cases. In psql if you don't specify a database it tries to connect, at least in *nix (any Unix like OS), to a database with the same name as the user in the operating system. Not sure Windows, but I would think it probably behaves the same.

The authentication mechanism doesn't know, or care, what operation you are about to do. It just needs to verify:

  • Does the user exist?
  • Did we get proper password for the user (unless you are setup with trust connections)?
  • Is the user trying to connect to a database that exists and it has rights to connect?
  • I can connect to template1 without specifying a username/password, or my own MyDB without a username./password. I just wondered why an operation working at the cluster level would need DB level authentication – TSG Feb 11 '18 at 20:08
  • I can connect to template1 without specifying a username/password - Possibly trust authentication is allowed in pg_hba.conf, which is a really bad practice. Your own DB is likely using ident; a user can connect to his own DB without a password. What you need to keep in mind is not the operation you are trying to do, but the fact that any and all connections require you to connect to a specific DB. – Francisco1844 Feb 12 '18 at 4:22

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.