initlocation not working in postgresql

Question

I am using postgresql 9.2 on a RHEL 6.5 machine and would like to set up a database in a non-standard location as it is filling up the root partition. The instructions tell me to use the initlocation command from the command line but I get the error 'command not found' on using it. I have searched for it in case it is a shell script but cannot find it anywhere. How do I run this command? thanks for any help.

Answer 1

Right, so you want to leave the existing installation where it is and create a new database in a directory that has more space.

First, a disclaimer, I haven't done exactly this before, but, I have a 9.4b2 installation here on my home computer and I did walk through these steps to verify that is seems to work :-)

@Mike Sherrill 'Cat Recall' has a pretty good write up on database storage (Where does PostgreSQL store the database?). I don't think it specifically answers your question, but it does have all of the information necessary to figure it out.

In your case, you will want to use tablespaces. First, create a new tablespace in the postgres installation. I will use my machine as an example. The first thing I would do is identify where the new database will reside. A directory name, and it has to be empty. For me, I'll create an empty directory in my home directory (I have to do it as the root user) with the shell commands:

mkdir /home/gfausak/pg_tablespace
chown postgres /home/gfausak/pg_tablespace
chgrp postgres /home/gfausak/pg_tablespace
chmod 700 /home/gfausak/pg_tablespace

Then I log in to psql as the postgres user. Like:

psql -Upostgres

Your incantation might be different. When you get to the prompt just verify where your current database(s) are via:

postgres=# show data_directory;
    data_directory 
-----------------------
 /var/local/pgsql/data
(1 row)

Now, create your new tablespace.

postgres=# create tablespace newspace location '/home/gfausak/pg_tablespace';
CREATE TABLESPACE

The docs for this command can be views here: http://www.postgresql.org/docs/9.2/static/manage-ag-tablespaces.html

Once created it can be used for a variety of different objects. In your case, createdb, can be done from the command line or from the postgres psql prompt. I'll create a test database in that tablespace:

postgres=# create database newdb with tablespace = newspace;
CREATE DATABASE

Just for grins, open it and create a table:

postgres=# \c newdb
You are now connected to database "newdb" as user "postgres".
newdb=# create table testme (t text);
CREATE TABLE
newdb=# 

If I go back to my home directory and take a peek in that newly created directory I'll see some postgres files:

ls /home/gfausak/pg_tablespace
PG_9.4_201407151

Anything you create in the newdb database will go in to the new directory unless you override the creation of it. There is a default_tablespace variable. As long as it is blank, the default is to put objects in the current database. Same goes for another setting temp_tablespace. The default for both of these is blank, meaning the current database.

You can even create objects in your old database in the new tablespace by specifying the tablespace when you create the table.

-g