Thread: How to find data directory

How to find data directory

From
Ken Hill
Date:
I installed postgresql using the Synaptic Package Manager on my Ubuntu
distro. I created the 'postgres' user account. I can create users and
database, but I can find the directory where the database are kept. How
do I find where the postgresql database directory is from a command
line?


Re: How to find data directory

From
Tom Lane
Date:
Ken Hill <ken@scottshill.com> writes:
> How do I find where the postgresql database directory is from a command
> line?

Since 8.0 you can do "show data_directory".

            regards, tom lane

Re: How to find data directory

From
Jeff Frost
Date:
Run the following query:

show data_directory;


On Fri, 16 Dec 2005, Ken Hill wrote:

> database, but I can find the directory where the database are kept. How
> do I find where the postgresql database directory is from a command
> line?

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

Re: How to find data directory

From
Ken Hill
Date:
I'm using 7.4.8 so that does not work. Any other suggestions?

On Fri, 2005-12-16 at 15:50 -0800, Jeff Frost wrote:
> Run the following query:
>
> show data_directory;
>
>
> On Fri, 16 Dec 2005, Ken Hill wrote:
>
> > database, but I can find the directory where the database are kept. How
> > do I find where the postgresql database directory is from a command
> > line?
>


Re: How to find data directory

From
Jeff Frost
Date:
Then an easy way is likely to look at the init script that starts it, or use
ps auxwww |grep postmaster to have a look at how it was started.  The last
command will likely yield something that looks like this:

postgres  3026  0.0  0.3  20064  3176 ?        S    08:21   0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data

Which means the data directory is /var/lib/pgsql/data.

>>> database, but I can find the directory where the database are kept. How
>>> do I find where the postgresql database directory is from a command
>>> line?

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

Re: How to find data directory

From
Ken Hill
Date:
That works nice. It shows my data directory in
'/var/lib/postgresql/7.4/main'. When I do an ls command, I get:

$ sudo ls /var/lib/postgresql/7.4/main
base    pg_clog      pg_ident.conf  pg_xlog          postmaster.opts
root.crt
global  pg_hba.conf  PG_VERSION     postgresql.conf  postmaster.pid

I have created two database named 'testdb' and 'csalgorithm'. How do I
find these databases? I was expecting the databases to be subdirectories
under the database directory (e.g. /var/lib/postresql/7.4/main/testdb
and /var/lib/postgresql/7.4/main/csalgorithm).

On Fri, 2005-12-16 at 19:34 -0800, Jeff Frost wrote:
> Then an easy way is likely to look at the init script that starts it, or use
> ps auxwww |grep postmaster to have a look at how it was started.  The last
> command will likely yield something that looks like this:
>
> postgres  3026  0.0  0.3  20064  3176 ?        S    08:21   0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
>
> Which means the data directory is /var/lib/pgsql/data.
>
> >>> database, but I can find the directory where the database are kept. How
> >>> do I find where the postgresql database directory is from a command
> >>> line?
>


Re: How to find data directory

From
Devrim GUNDUZ
Date:
Hi,

On Sun, 18 Dec 2005, Ken Hill wrote:

> I have created two database named 'testdb' and 'csalgorithm'. How do I
> find these databases? I was expecting the databases to be subdirectories
> under the database directory (e.g. /var/lib/postresql/7.4/main/testdb
> and /var/lib/postgresql/7.4/main/csalgorithm).

First run these commands:

SELECT oid,datname FROM pg_database;

This will prompt you the oid and the corresponding database name. Say the
oid for the csalgorithm is 41030. Now cd to
/var/lib/postgresql/7.4/main/base/41030

But don't expect to see ascii database format there. :)

Regards,
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/


Re: How to find data directory

From
Michael Fuhr
Date:
On Sun, Dec 18, 2005 at 04:34:16PM -0800, Ken Hill wrote:
> That works nice. It shows my data directory in
> '/var/lib/postgresql/7.4/main'. When I do an ls command, I get:
>
> $ sudo ls /var/lib/postgresql/7.4/main
> base    pg_clog      pg_ident.conf  pg_xlog          postmaster.opts
> root.crt
> global  pg_hba.conf  PG_VERSION     postgresql.conf  postmaster.pid
>
> I have created two database named 'testdb' and 'csalgorithm'. How do I
> find these databases? I was expecting the databases to be subdirectories
> under the database directory (e.g. /var/lib/postresql/7.4/main/testdb
> and /var/lib/postgresql/7.4/main/csalgorithm).

Database directories are under the "base" directory with names that
match their oid in pg_database.  So if pg_database has

SELECT oid, datname FROM pg_database;
  oid  |  datname
-------+-----------
     1 | template1
 17141 | template0
(2 rows)

then the files for template1 will be under base/1 and the files for
template0 will be under base/17141.

In versions 8.0 and later you can control where database files go
by using tablespaces, and earlier versions had an "alternative
location" mechanism.  See the "Managing Databases" chapter in the
documentation to learn about those features.

--
Michael Fuhr

Re: How to find data directory

From
Peter Eisentraut
Date:
Ken Hill wrote:
> I have created two database named 'testdb' and 'csalgorithm'. How do I
> find these databases?

Unless you are an expert user, you probably shouldn't be trying to find
them at all.  All database administration is done through the frontend.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: How to find data directory

From
Ken Hill
Date:
There they are:

csalgorithm=# SELECT oid,datname FROM pg_database;
  oid  |   datname
-------+-------------
 17142 | testdb
 17143 | csalgorithm
     1 | template1
 17141 | template0
(4 rows)

Thank you for the help!

On Mon, 2005-12-19 at 03:01 +0200, Devrim GUNDUZ wrote:
> Hi,
>
> On Sun, 18 Dec 2005, Ken Hill wrote:
>
> > I have created two database named 'testdb' and 'csalgorithm'. How do I
> > find these databases? I was expecting the databases to be subdirectories
> > under the database directory (e.g. /var/lib/postresql/7.4/main/testdb
> > and /var/lib/postgresql/7.4/main/csalgorithm).
>
> First run these commands:
>
> SELECT oid,datname FROM pg_database;
>
> This will prompt you the oid and the corresponding database name. Say the
> oid for the csalgorithm is 41030. Now cd to
> /var/lib/postgresql/7.4/main/base/41030
>
> But don't expect to see ascii database format there. :)
>
> Regards,


Re: How to find data directory

From
Chris Browne
Date:
ken@scottshill.com (Ken Hill) writes:
> That works nice. It shows my data directory in
> '/var/lib/postgresql/7.4/main'. When I do an ls command, I get:
>
> $ sudo ls /var/lib/postgresql/7.4/main
> base    pg_clog      pg_ident.conf  pg_xlog          postmaster.opts
> root.crt
> global  pg_hba.conf  PG_VERSION     postgresql.conf  postmaster.pid
>
> I have created two database named 'testdb' and 'csalgorithm'. How do I
> find these databases? I was expecting the databases to be subdirectories
> under the database directory (e.g. /var/lib/postresql/7.4/main/testdb
> and /var/lib/postgresql/7.4/main/csalgorithm).

They'll be in a subdirectory; the thing is, tables and databases are
not named _by name_.

The filenames are the OID numbers associated with either databases
(see pg_catalog.pg_database) or tables (see pg_catalog.pg_class).
--
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/unix.html
"The Linux  philosophy is laugh in  the face of  danger.  Oops.  Wrong
One.  'Do it yourself.'  That's it."  -- Linus Torvalds

Re: How to find data directory

From
Michael Fuhr
Date:
On Sun, Dec 18, 2005 at 09:56:39PM -0500, Chris Browne wrote:
> The filenames are the OID numbers associated with either databases
> (see pg_catalog.pg_database) or tables (see pg_catalog.pg_class).

For tables, indexes, etc., the filename isn't necessarily the same
as the object's oid; it starts out that way but it can change after
commands like TRUNCATE and CLUSTER.  pg_class.relfilenode contains
the name of a relation's on-disk file.

--
Michael Fuhr