3

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.

andrewp
  • 73
  • 1
  • 4
  • do you want to install the postgres installation so that the data files are in a different directory, or do you want to modify an existing installation placing a single database in a different directory? – Greg Sep 09 '14 at 16:52
  • Link to instructions? – Mike Sherrill 'Cat Recall' Sep 09 '14 at 17:02
  • @greg I have an existing installation that puts data files into the root partition by default and would like to modify this existing installation to create a new database where the data files are stored in my home area. I'd like to put future data files into my home area too as it is much bigger. – andrewp Sep 09 '14 at 20:51
  • @ Mike Sherrill 'Cat Recall' http://www.postgresql.org/docs/7.4/static/manage-ag-alternate-locs.html – andrewp Sep 10 '14 at 12:49

2 Answers2

6

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

Community
  • 1
  • 1
Greg
  • 6,571
  • 2
  • 27
  • 39
  • I hadn't imagined the last bit (new objects in new location for old database) was possible. that is even better. I'll give this a try – andrewp Sep 10 '14 at 12:56
0

Try this.


vi /etc/init.d/postgresql-XX 

Change this variable with:
PGDATA=/var/lib/pgsql/$PGMAJORVERSION/data
PGLOG=/var/lib/pgsql/$PGMAJORVERSION/pgstartup.log

on:
PGDATA=/new_pgdata/pgsql/${PGMAJORVERSION}/data
PGLOG=/new_pgdata/pgsql/${PGMAJORVERSION}/pgstartup.log

than:
mkdir -p /new_pgdata/pgsql/XX/data/
chown -R postgres:postgres /pgdata/pgsql

/etc/init.d/postgresql-XX initdb -D /new_pgdata/pgsql/XX/data/

Where XX is equal to 9.2
Rhim
  • 644
  • 6
  • 12