Thread: How to find data directory
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?
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
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
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? >
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
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? >
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/
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
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/
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,
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
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