Postgresql – Will PostgreSQL server work if I install the service in mounted partition

postgresqlpostgresql-9.1restore

I have created all tablespaces (only tables created in these tablespaces) in mounted partition, But db is available in pg_default tablespace (root partition where postgresql installed).

I am facing problem with memory since my root partition having only 10 GB. I have verified the data folder (/opt/postgresql/9.1/data/), and noticed few things.

/opt/postgresql/9.1/data/base directory having more data
pg_largeobject table is having more data

So, I planned to do following activity,

Take the full backup of existing database(s) – custom format (*.sql)
Uninstall the postgresql from the root directory, using uninstall.sh (which is available the postgresql path)
Install the new postgresql in mounted path (/newly/mounted/remote/path/postgresql/)

If I do above steps, will postgresql service start and work without any issue?
(if no issue, I will restore the database backup)

Any suggestion?

(using postgresql-9.1)

Best Answer

Yes, you can move your base directory to any location you wish where the postgres service has write access and enough storage available. There are a few things to note

your startup script needs to set it's PGDATA directory correctly to the new location
your clients that connect locally have to set their PGDATA to the new location. Tablespaces in postgres add a lot to complexity. Better is to try to prevent them being used at all.

Tablespaces are directories that are pointed to by symbolic links in $PGDATA so you can keep them if you like. They will be resolved.

Related Solutions

PostgreSQL: Query for location of global tablespace

pg_default and pg_global locations are "hardcoded".

pg_default lives in:

select setting||'/base' from pg_settings where name='data_directory';

and pg_global lives in:

select setting||'/global' from pg_settings where name='data_directory';

src/backend/commands/tablespace.c says so:

 * There are two tablespaces created at initdb time: pg_global (for shared
 * tables) and pg_default (for everything else).  For backwards compatibility
 * and to remain functional on platforms without symlinks, these tablespaces
 * are accessed specially: they are respectively
 *          $PGDATA/global/relfilenode
 *          $PGDATA/base/dboid/relfilenode

Please also note, that exposing location of data directory is a - not so terrible but still - security hole.

app01@postgres=> show data_directory;
ERROR:  must be superuser to examine "data_directory"

Postgresql – How to change the data directory on Postgresql 9.1 on Ubuntu

If you create a cluster in Debian/Ubuntu without specifying the owner (option --user of pg_createcluster) then you have to be sure that the directory you specify is accessibile in r/w to the postgres user. Please note that all parent directories should also be accessibile to the same user with bit x (list directory content).

Otherwise, you may specify a different owner using option --user. This should create a cluster ran by that user instead of postgres.

In order to check if postgres user may access that directory, execute this command:

sudo su - postgres -c 'touch /home/fccoelho/Documentos/databases/postgresqldata/test'

and check for any error.

Best Answer

Related Solutions

PostgreSQL: Query for location of global tablespace

Postgresql – How to change the data directory on Postgresql 9.1 on Ubuntu

Related Question