2

I would like to make a backup of a postgres database file that I created on Ubuntu 16.04.

I cannot find the file. Some other sources indicate it should be under:

/var/lib/postgresql/[version]/data

When I run show data_directory inside of postgres it shows:

/var/lib/postgresql/9.5/main

However there is no data folder there.

2
  • 6
    Depends on how you installed postgres. Check pg_lscluster if you used the Ubuntu packages, and it'll show you where. But anyway, don't just copy the datadir for backups, use pg_basebackup or pg_dump and pg_dumpall. See postgresql.org/docs/current/static/backup.html Jun 29, 2017 at 0:37
  • 3
    @CraigRinger I think this should be an actual answer. Jun 29, 2017 at 8:10

1 Answer 1

2

To find out where the PostgreSQL data folder is located, just run SQL query:

show data_directory;

You can find more details here

However, note

File System Level Backup are faster but large in size. The worst thing is that to perform a database's backup, PostgreSQL Server required to be stopped. More information about the restrictions of this operation can be found in the official documentation. On my own I will add that from such backups, as a rule, there is more harm than good.

The main method for creating backups in PostgreSQL is the pg_dump utility.

pg_dump dbname > dbname.dump

Opposite file-level backup, a backup via the pg_dump tool does not require stopping the server, it is easy to automate and does not require access to the server file system (it can be performed remotely).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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