Starting postgresql service and specifying data directory

Question

I have recently installed pg 8.4 on Ubuntu 10.0.4 using apt-get install.

It installed pg as a service which starts ehenever the machine starts. However I have created a db cluster in a specific location that I want postgres to use.

This is the command line output of how I created the cluster:

username@jupiter:~$ sudo /usr/lib/postgresql/8.4/bin/initdb --encoding=UTF8 --pgdata=/mydata/pgdbdata/
initdb: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
username@jupiter:~$ /usr/lib/postgresql/8.4/bin/initdb --encoding=UTF8 --pgdata=/mydata/pgdbdata/
The files belonging to this database system will be owned by user "username".
This user must also own the server process.
The database cluster will be initialized with locale en_US.utf8.
The default text search configuration will be set to "english".
fixing permissions on existing directory /mydata/pgdbdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 24MB
creating configuration files ... ok
creating template1 database in /mydata/pgdbdata/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.
Success. You can now start the database server using:
 /usr/lib/postgresql/8.4/bin/postgres -D /mydata/pgdbdata
or
 /usr/lib/postgresql/8.4/bin/pg_ctl -D /mydata/pgdbdata -l logfile start
username@jupiter:~$ 

Now, I try to start the postgresql service:

Here is the command line output:

username@jupiter:~$ /usr/lib/postgresql/8.4/bin/pg_ctl start -D /mydata/pgdbdata/
server starting
username@jupiter:~$ FATAL: could not create lock file "/var/run/postgresql/.s.PGSQL.5432.lock": Permission denied

So I have two questions:

1. How can I start the service and specify a data location (what am I doing wrong?)

2. I want the service started when the machine starts/reboots, to use the new data directory. Which file do I need to edit to ensure that I always have the service using the correct data folder?

• user1822: I don't know Ubuntu (or Linux), but did you try to set the PGDATA environment variable for the user account running the service?

Answer 1

There error is telling ... The problem is that you ran initdb as your local login, but I'm pretty that your login (username) doesn't have access to /var/run/postgresql. If you have a clean install, may I recommend that you remove the data directory and start all over, this time by su-ing into the postgres account:

username@jupiter:~$ sudo su - postgres

Then you can initdb

postgres@jupiter:~$ /usr/lib/postgresql/8.4/bin/initdb --encoding=UTF8 --pgdata=/mydata/pgdbdata/

Also, as Catcall mentioned, make sure you're using the correct location for your data.

• Homunculus Reticulli: +1: I think you are on the right track. I recently added a new drive purposely for the data. I think there are some permission issues with the mount. I will resolve that and see if the issue goes away.
• swasheck: Is this existing data that was moved to a new drive?
• Homunculus Reticulli: No. I am in the process of importing a huge dataset into pg.
• swasheck: if you're moving your existing data from an old drive to a new drive then things will be a bit different. if you're restoring from a dump then i'd recommend creating your new default database, then restoring the dump into the default location.
• Homunculus Reticulli: No. Clarification: this is a brand new data import.

Answer 2

Instead of running initdb, use pg_createcluster in the first place. The problems you're trying to solve are already taken care of when using the tools provided by the debian/ubuntu packages to handle multiple clusters and multiple PG server versions simultaneously:

See also:
pg_wrapper
pg_dropcluster
pg_ctlcluster
pg_lsclusters

Answer 3

When you created the cluster, you seem to have used the directory /mydata/pgdbdata/. But when you try to start the service, you seem to be trying to use the directory /marketdata/pgdbdata/.

• Homunculus Reticulli: typo. since corrected.