Обсуждение: Location of databases

I have just installed PostgreSQL, with a view to using it in conjunction with PSPP and also with OpenOffice Base.  I have created a test database called exampledb with a table in it.  Where do I locate the database, please?  A file search for the name reveals nothing in the filesystem, although the database and table are obviously there.

Regards

John Shipley

Hull, England

---

Share your memories online with anyone you want. Learn more.

john shipley schrieb:
> I have just installed PostgreSQL, with a view to using it in conjunction
> with PSPP and also with OpenOffice Base.  I have created a test database
> called exampledb with a table in it.  Where do I locate the database,
> please?  A file search for the name reveals nothing in the filesystem,
> although the database and table are obviously there.

which OS are you running? And which PostgreSQL did you install? Package, self compiled,
one click installer?

Cheers

Andy

2009/7/27 john shipley <johnwshipley@hotmail.co.uk>:
> I have just installed PostgreSQL, with a view to using it in conjunction
> with PSPP and also with OpenOffice Base.  I have created a test database
> called exampledb with a table in it.  Where do I locate the database,
> please?  A file search for the name reveals nothing in the filesystem,
> although the database and table are obviously there.

MySQL stores the database in a directory with the same name and the
tables in files with the names based on the table names.  PostgreSQL
works differently.  It does not store the databases/tables in files or
directories that are named after the databases/tables.  For small
databases or ones where you don't need to worry too much about
performance you don't really need to know where the files are
(depending on how you're doing backups.)

On Debian and Ubuntu the files are stored under
/var/lib/postgresql/VERSION/main, e.g. /var/lib/postgresql/8.3/main.
Also, a "postgres" user gets created with /var/lib/postgresql as its
home directory, so you can "cd ~postgres" to get there.

On FreeBSD I think the username is pgsql as far as I remember, but I
think you can do the same "cd ~pgsql" trick there to find where the
files are being kept.

I believe you can tell PostgreSQL to store the data somewhere else by
using tablespaces (which I've never looked at, so I don't know the
details.)

--
Michael Wood <esiotrot@gmail.com>

john shipley <johnwshipley@hotmail.co.uk> writes:
> I have just installed PostgreSQL, with a view to using it in conjunction with PSPP and also with OpenOffice Base.  I
havecreated a test database called exampledb with a table in it.  Where do I locate the database, please?  A file
searchfor the name reveals nothing in the filesystem, although the database and table are obviously there. 

"show data_directory" would tell you where it is ... although usually
the information is of just academic interest.

            regards, tom lane

Tom Lane wrote:
> john shipley <johnwshipley@hotmail.co.uk> writes:
>> I have just installed PostgreSQL, with a view to using it in
>> conjunction with PSPP and also with OpenOffice Base.  I have
>> created a test database called exampledb with a table in it.  Where
>> do I locate the database, please?  A file search for the name
>> reveals nothing in the filesystem, although the database and table
>> are obviously there.
>
> "show data_directory" would tell you where it is ... although usually
>  the information is of just academic interest.

Until you run out of space on that disk drive/partition.

Bob McConnell

john shipley schrieb:
> Thank you for your reply.  I am using PostgreSQL 8.3 with Ubuntu Linux
> 9.04.  I installed it using the command line package manager APT-GET.
> Naturally, I had to change my password and set myself up as user
> manually.
> Judging by the other replies I have received, I am not making myself clear.
>
> I wish to use PostgreSQL with two other programmes.  One is an open
> source statistics package called PSPP.  I don't know if you are familiar
> with it.  The present form of PSPP is 'under development'.  Whilst there
> is a graphical user interface, PSPP relies extensively on command line
> operation.  Indeed, certain of its features can only be accessed through
> the command line.  When I use the term 'command line', I am, of course,
> referring to a script file or batch file (call it what you will),
> containing a list of instructions.  In order to load the contents of a
> PostgreSQL file into PSPP, I have to show its location and name and
> indicate that is is a PostgreSQL file by means of a statement like the
> following:
>
> FILE = /home/myusername/.../.../.../ filenamedb(PSQL).
>
> Similarly, if I wish to connect to a PostgreSQL file via the Open Office
> RDBMS, in order to use that medium to construct a query visually, I have
> to point to the location of the file using a file manager.
>
> Therefore, I need to know where filenamedb is.  In other words, what are
> '/.../.../.../'?

actually I don't know what PSPP is.

It is possible to install more PostgreSQL versions side by side using a different port for
each. With initdb you have to give the data directory for PG. There, all the necessary
data for the cluster are saved. Ubuntu is creating for each PostgreSQL version an own
folder as shown below.

In Debian based OS you will find all needed files in the following directoy's:

# home directory of user postgres (main is the so called data directory):
/var/lib/postgresql/8.3/main/

base
global
pg_clog
pg_multixact
pg_subtrans
pg_tblspc
pg_twophase
PG_VERSION
pg_xlog
postmaster.opts
postmaster.pid

The data for each database are located in a subfolder of "base" named with its OID. I
suppose this will be the folder you need or - dependent to your aqpplication - the main
directory shown above.

# configuration files:
/etc/postgresql/8.3/main/


# additional stuff:
/usr/share/postgresql/8.3/

Hope this helps ...

Cheers

Andy

2009/7/28 Andreas Wenk <a.wenk@netzmeister-st-pauli.de>:
> john shipley schrieb:
[...]
>> In order to load the contents of a PostgreSQL file into PSPP,
>> I have to show its location and name and indicate that is is a PostgreSQL
>> file by means of a statement like the following:
>>
>> FILE = /home/myusername/.../.../.../ filenamedb(PSQL).

I don't know anything about PSPP, but you cannot directly access the
files used by PostgreSQL.  You have to use a client like psql or
ODBC/JDBC or the PostgreSQL client libraries to talk to PostgreSQL and
PostgreSQL reads/writes the files itself.  This is why the location of
the files is generally not something you need to know.  What you do
need to know is things like the IP address of the machine running
PostgreSQL and the port number that PostgreSQL is running on (unless
you're on the same machine and you're using a Unix domain socket to
talk to PostgreSQL.)

The above is the same for MySQL, Oracle, MS SQL Server, IBM DB2 etc.
As far as I know it is only embedded databases like Sqlite and MS
Access where you need to know the location of the file in order to
access the database.

>> Similarly, if I wish to connect to a PostgreSQL file via the Open Office
>> RDBMS, in order to use that medium to construct a query visually, I have to
>> point to the location of the file using a file manager.

No, you need to use the "Connect to an existing database" option and
choose "JDBC".  You will then need to specify the JDBC datasource URL.
 I can't help you there off the top of my head, but the syntax should
be in the documentation for the PostgreSQL JDBC driver I suppose.

I hope that clarifies things a bit :)

--
Michael Wood <esiotrot@gmail.com>

Michael Wood schrieb:
> 2009/7/28 Andreas Wenk <a.wenk@netzmeister-st-pauli.de>:
>> john shipley schrieb:
> [...]
>>> In order to load the contents of a PostgreSQL file into PSPP,
>>> I have to show its location and name and indicate that is is a PostgreSQL
>>> file by means of a statement like the following:
>>>
>>> FILE = /home/myusername/.../.../.../ filenamedb(PSQL).
>
> I don't know anything about PSPP, but you cannot directly access the
> files used by PostgreSQL.  You have to use a client like psql or
> ODBC/JDBC or the PostgreSQL client libraries to talk to PostgreSQL and
> PostgreSQL reads/writes the files itself.  This is why the location of
> the files is generally not something you need to know.  What you do
> need to know is things like the IP address of the machine running
> PostgreSQL and the port number that PostgreSQL is running on (unless
> you're on the same machine and you're using a Unix domain socket to
> talk to PostgreSQL.)

hm - I think you are right. So maybe this PSPP is not working with a database like PG at
all. Or something else has to be set up.

My focus was more to show where the files and direcotries are located generally. Maybe
this was not helping to find a solution ...

Cheers

Andy

Andreas Wenk wrote:
> Michael Wood schrieb:
>> 2009/7/28 Andreas Wenk <a.wenk@netzmeister-st-pauli.de>:
>>> john shipley schrieb:
>> [...]
>>>> In order to load the contents of a PostgreSQL file into PSPP,
>>>> I have to show its location and name and indicate that is is a
>>>> PostgreSQL
>>>> file by means of a statement like the following:
>>>>
>>>> FILE = /home/myusername/.../.../.../ filenamedb(PSQL).
>>
>> I don't know anything about PSPP, but you cannot directly access the
>> files used by PostgreSQL.  You have to use a client like psql or
>> ODBC/JDBC or the PostgreSQL client libraries to talk to PostgreSQL and
>> PostgreSQL reads/writes the files itself.  This is why the location of
>> the files is generally not something you need to know.  What you do
>> need to know is things like the IP address of the machine running
>> PostgreSQL and the port number that PostgreSQL is running on (unless
>> you're on the same machine and you're using a Unix domain socket to
>> talk to PostgreSQL.)
>
> hm - I think you are right. So maybe this PSPP is not working with a
> database like PG at all. Or something else has to be set up.
>
> My focus was more to show where the files and direcotries are located
> generally. Maybe this was not helping to find a solution ...
>
> Cheers
>
> Andy
>

Havent used PSPP but if its the one available on gnu.org a quick flick
thru the docs reveals it will work with postgres. It uses LIBPQ-Connect
for its connection.

The format is (eg)
GET DATA /TYPE=PSQL
           /CONNECT='host=example.com port=5432 dbname=product user=fred
passwd=xxxx'
           /SQL='select * from manufacturer'.
Naturally you insert your stuff.

Peter Jackson

(and my apologies to the list maintainers over my lousy mail sending
skills lately)
(aarrggh I did it again!)(must remember use the registered address not
the other address)

Andreas Wenk wrote:
> Michael Wood schrieb:
>> 2009/7/28 Andreas Wenk <a.wenk@netzmeister-st-pauli.de>:
>>> john shipley schrieb:
>> [...]
>>>> In order to load the contents of a PostgreSQL file into PSPP,
>>>> I have to show its location and name and indicate that is is a
>>>> PostgreSQL
>>>> file by means of a statement like the following:
>>>>
>>>> FILE = /home/myusername/.../.../.../ filenamedb(PSQL).
>>
>> I don't know anything about PSPP, but you cannot directly access the
>> files used by PostgreSQL.  You have to use a client like psql or
>> ODBC/JDBC or the PostgreSQL client libraries to talk to PostgreSQL and
>> PostgreSQL reads/writes the files itself.  This is why the location of
>> the files is generally not something you need to know.  What you do
>> need to know is things like the IP address of the machine running
>> PostgreSQL and the port number that PostgreSQL is running on (unless
>> you're on the same machine and you're using a Unix domain socket to
>> talk to PostgreSQL.)
>
> hm - I think you are right. So maybe this PSPP is not working with a
> database like PG at all. Or something else has to be set up.
>
> My focus was more to show where the files and direcotries are located
> generally. Maybe this was not helping to find a solution ...
>
> Cheers
>
> Andy
>

Havent used PSPP but if its the one available on gnu.org a quick flick
thru the docs reveals it will work with postgres. It uses LIBPQ-Connect
for its connection.

The format is (eg)
GET DATA /TYPE=PSQL
           /CONNECT='host=example.com port=5432 dbname=product user=fred
passwd=xxxx'
           /SQL='select * from manufacturer'.
Naturally you insert your stuff.

Peter Jackson

(and my apologies to the list maintainers over my lousy mail sending
skills lately)