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?
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.
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;
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;
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';
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
