pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to find data directory
12+ messages / 7 participants
[nested] [flat]

* How to find data directory
@ 2005-12-16 22:31  Ken Hill <ken@scottshill.com>
  0 siblings, 2 replies; 12+ messages in thread

From: Ken Hill @ 2005-12-16 22:31 UTC (permalink / raw)
  To: pgsql-admin

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?




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: How to find data directory
@ 2005-12-16 23:13  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Ken Hill <ken@scottshill.com>
  1 sibling, 0 replies; 12+ messages in thread

From: Tom Lane @ 2005-12-16 23:13 UTC (permalink / raw)
  To: ken@scottshill.com; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: How to find data directory
@ 2005-12-16 23:50  Jeff Frost <jeff@frostconsultingllc.com>
  parent: Ken Hill <ken@scottshill.com>
  1 sibling, 1 reply; 12+ messages in thread

From: Jeff Frost @ 2005-12-16 23:50 UTC (permalink / raw)
  To: Ken Hill <ken@scottshill.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: How to find data directory
@ 2005-12-17 01:47  Ken Hill <ken@scottshill.com>
  parent: Jeff Frost <jeff@frostconsultingllc.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Ken Hill @ 2005-12-17 01:47 UTC (permalink / raw)
  To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-admin

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?
> 




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: How to find data directory
@ 2005-12-17 03:34  Jeff Frost <jeff@frostconsultingllc.com>
  parent: Ken Hill <ken@scottshill.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Jeff Frost @ 2005-12-17 03:34 UTC (permalink / raw)
  To: Ken Hill <ken@scottshill.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: How to find data directory
@ 2005-12-19 00:34  Ken Hill <ken@scottshill.com>
  parent: Jeff Frost <jeff@frostconsultingllc.com>
  0 siblings, 4 replies; 12+ messages in thread

From: Ken Hill @ 2005-12-19 00:34 UTC (permalink / raw)
  To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-admin

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?
> 




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: How to find data directory
@ 2005-12-19 01:01  Devrim GUNDUZ <devrim@commandprompt.com>
  parent: Ken Hill <ken@scottshill.com>
  3 siblings, 1 reply; 12+ messages in thread

From: Devrim GUNDUZ @ 2005-12-19 01:01 UTC (permalink / raw)
  To: Ken Hill <ken@scottshill.com>; +Cc: pgsql-admin


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/




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: How to find data directory
@ 2005-12-19 01:04  Michael Fuhr <mike@fuhr.org>
  parent: Ken Hill <ken@scottshill.com>
  3 siblings, 0 replies; 12+ messages in thread

From: Michael Fuhr @ 2005-12-19 01:04 UTC (permalink / raw)
  To: Ken Hill <ken@scottshill.com>; +Cc: Jeff Frost <jeff@frostconsultingllc.com>; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: How to find data directory
@ 2005-12-19 01:06  Peter Eisentraut <peter_e@gmx.net>
  parent: Ken Hill <ken@scottshill.com>
  3 siblings, 0 replies; 12+ messages in thread

From: Peter Eisentraut @ 2005-12-19 01:06 UTC (permalink / raw)
  To: ken@scottshill.com; +Cc: pgsql-admin

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/



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: How to find data directory
@ 2005-12-19 01:29  Ken Hill <ken@scottshill.com>
  parent: Devrim GUNDUZ <devrim@commandprompt.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Ken Hill @ 2005-12-19 01:29 UTC (permalink / raw)
  To: Devrim GUNDUZ <devrim@commandprompt.com>; +Cc: pgsql-admin

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,




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: How to find data directory
@ 2005-12-19 02:56  Chris Browne <cbbrowne@acm.org>
  parent: Ken Hill <ken@scottshill.com>
  3 siblings, 1 reply; 12+ messages in thread

From: Chris Browne @ 2005-12-19 02:56 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: How to find data directory
@ 2005-12-19 05:43  Michael Fuhr <mike@fuhr.org>
  parent: Chris Browne <cbbrowne@acm.org>
  0 siblings, 0 replies; 12+ messages in thread

From: Michael Fuhr @ 2005-12-19 05:43 UTC (permalink / raw)
  To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread


end of thread, other threads:[~2005-12-19 05:43 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-16 22:31 How to find data directory Ken Hill <ken@scottshill.com>
2005-12-16 23:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-16 23:50 ` Jeff Frost <jeff@frostconsultingllc.com>
2005-12-17 01:47   ` Ken Hill <ken@scottshill.com>
2005-12-17 03:34     ` Jeff Frost <jeff@frostconsultingllc.com>
2005-12-19 00:34       ` Ken Hill <ken@scottshill.com>
2005-12-19 01:01         ` Devrim GUNDUZ <devrim@commandprompt.com>
2005-12-19 01:29           ` Ken Hill <ken@scottshill.com>
2005-12-19 01:04         ` Michael Fuhr <mike@fuhr.org>
2005-12-19 01:06         ` Peter Eisentraut <peter_e@gmx.net>
2005-12-19 02:56         ` Chris Browne <cbbrowne@acm.org>
2005-12-19 05:43           ` Michael Fuhr <mike@fuhr.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox