7

So this might be a stupid question, but I'm really new to this. So I'm making a Postgres database on the postgres server, that I started up with this command:

sudo -u postgres psql

And then I did the command:

CREATE DATABASE database;

Which supposedly created the database. But where is this database? Where can I find it on my computer?

  • 1
    Why do you want to know that ? Use pg_dump for backup. – Luc M Mar 15 '13 at 1:08
  • 2
    I recommend starting with postgresql.org/docs/current/static/tutorial.html . And you didn't start PostgreSQL with the psql command, you connected to it; it's a server and psql is the client. – Craig Ringer Mar 15 '13 at 1:17
  • 1
    The "database" is not a single file that you can take with you or look at. A database server manages this differently than e.g. Microsoft Access. – a_horse_with_no_name Mar 15 '13 at 9:30
9

Where exactly your data is stored depends on how you installed and configured PostgreSQL. Running the

show data_directory

command as the postgres user (from inside the psql tool or another client) will tell you where it is though. For example, if you installed it from the debian or ubuntu repositories you would probably find your data files are somewhere like /var/lib/postgresql/9.2/main.

There probably isn't anything terribly interesting in there though. Sometimes there are a few configuration files like pg_hba.conf (the windows version puts them there at least) but if you're on debian you'll find those under /etc/postgresql/.

If you're expecting your database to be a single file that you can move around between systems, etc, you're probably better off using a simpler embedded database like sqlite.

6

To see where the data directory is, use this query.

show data_directory;

To see all the run-time parameters, use

show all;

You can create tablespaces in other parts of the filesystem. To see the location of tablespaces, which might be somewhere in the filesystem besides the data directory, use this query.

SELECT * FROM pg_tablespace;
  • +1 on mentioning tablespaces (and how to get the information). – Erwin Brandstetter Mar 16 '13 at 2:12
1

when you execute the "CREATE DATABASE database" command, it uses the pg_default tablespace;

you can create your own tablespace first,such as

CREATE TABLESPACE tbs_01 [ OWNER user_name ] LOCATION 'directory';

and than create your database:

CREATE DATABASE datname
WITH  OWNER = user_name 
      TEMPLATE = template0 
      ENCODING = 'UTF8' 
      TABLESPACE = tbs_01;
0

Everyone already answered but just for the latest updates. If you want to know where all the configuration files reside then run this command in the shell

SELECT name, setting FROM pg_settings WHERE category = 'File Locations';
0

Debian / Ubuntu

Ubuntu and Debian displays all of this with pg_lsclusters -- one of the many administrators utilities they add. The output looks something like this

Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log

If you just want the Data directory, then | cut -d " " -f 6

pg_lsclusters -h | cut -d " " -f 6
/var/lib/postgresql/9.5/main

Your Answer

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