{"id":515,"date":"2014-04-22T08:29:28","date_gmt":"2014-04-22T07:29:28","guid":{"rendered":"http:\/\/it.tuxie.eu\/?p=515"},"modified":"2014-04-22T08:38:58","modified_gmt":"2014-04-22T07:38:58","slug":"change-data-directory-postgresql","status":"publish","type":"post","link":"http:\/\/it.tuxie.eu\/?p=515","title":{"rendered":"Change data directory – PostgreSQL"},"content":{"rendered":"

Useful and common task, should be well documented. Let\u00b4s try it! Debian 7.4 x64, PostgreSQL 9.3.2<\/p>\n

<\/p>\n

I just checked via psql where is current data directory stored. Default path =\u00a0\/var\/lib\/postgresql\/9.3\/main . As a root i will create new directory, \/PGDATA and assign ownership into postgres user:<\/p>\n

root@deb74:\/var\/lib\/postgresql\/9.3\/main# mkdir -p \/PGDATA<\/strong><\/span><\/span>
\nroot@deb74:\/var\/lib\/postgresql\/9.3\/main# chown -R postgres:postgres \/PGDATA<\/strong><\/span><\/span>
\nroot@deb74:\/var\/lib\/postgresql\/9.3\/main# service postgresql stop<\/strong><\/span><\/span>
\n[ ok ] Stopping PostgreSQL 9.3 database server: main.<\/span><\/p>\n

Last step = pure paranoia level and untrustful „restart“ service parameter \ud83d\ude42<\/p>\n

As postgres user I will re-initialize database:<\/p>\n

postgres@deb74:\/usr\/lib\/postgresql\/9.3\/bin$ \/usr\/lib\/postgresql\/9.3\/bin\/initdb -D \/PGDATA<\/strong><\/span><\/span>
\nThe files belonging to this database system will be owned by user „postgres“.<\/span>
\nThis user must also own the server process.<\/span><\/p>\n

The database cluster will be initialized with locale „en_US.UTF-8“.<\/span>
\nThe default database encoding has accordingly been set to „UTF8“.<\/span>
\nThe default text search configuration will be set to „english“.<\/span><\/p>\n

Data page checksums are disabled.<\/span><\/p>\n

fixing permissions on existing directory \/PGDATA … ok<\/span>
\ncreating subdirectories … ok<\/span>
\nselecting default max_connections … 100<\/span>
\nselecting default shared_buffers … 128MB<\/span>
\ncreating configuration files … ok<\/span>
\ncreating template1 database in \/PGDATA\/base\/1 … ok<\/span>
\ninitializing pg_authid … ok<\/span>
\ninitializing dependencies … ok<\/span>
\ncreating system views … ok<\/span>
\nloading system objects‘ descriptions … ok<\/span>
\ncreating collations … ok<\/span>
\ncreating conversions … ok<\/span>
\ncreating dictionaries … ok<\/span>
\nsetting privileges on built-in objects … ok<\/span>
\ncreating information schema … ok<\/span>
\nloading PL\/pgSQL server-side language … ok<\/span>
\nvacuuming database template1 … ok<\/span>
\ncopying template1 to template0 … ok<\/span>
\ncopying template1 to postgres … ok<\/span>
\nsyncing data to disk … ok<\/span><\/p>\n

WARNING: enabling „trust“ authentication for local connections<\/span>
\nYou can change this by editing pg_hba.conf or using the option -A, or<\/span>
\n–auth-local and –auth-host, the next time you run initdb.<\/span><\/p>\n

Success. You can now start the database server using:<\/span><\/p>\n

\/usr\/lib\/postgresql\/9.3\/bin\/postgres -D \/PGDATA<\/span>
\nor<\/span>
\n \/usr\/lib\/postgresql\/9.3\/bin\/pg_ctl -D \/PGDATA -l logfile start<\/span><\/p>\n

Change in configuration must be written into main postgresql conf file:<\/p>\n

postgres@deb74:~$ cd \/etc\/postgresql\/9.3\/main\/<\/strong><\/span><\/span><\/p>\n

postgres@deb74:\/etc\/postgresql\/9.3\/main$ nano postgresql.conf<\/strong><\/span><\/span><\/p>\n

There you have to change data_directory into '\/PGDATA'<\/code><\/strong><\/span>, save file.<\/p>\n

Now I need just to start postgresql service and double-check if everything is changed well:<\/p>\n

root@deb74:~# service postgresql start<\/strong><\/span><\/span>
\n[ ok ] Starting PostgreSQL 9.3 database server: main.<\/span>
\nroot@deb74:~# su postgres<\/strong><\/span><\/span>
\npostgres@deb74:\/root$ psql<\/strong><\/span><\/span>
\ncould not change directory to „\/root“: Permission denied<\/span>
\npsql (9.3.2)<\/span>
\nType „help“ for help.<\/span><\/p>\n

postgres=# show data_directory;<\/strong><\/span><\/span>
\ndata_directory <\/span>
\n—————-<\/span>
\n \/PGDATA<\/span>
\n(1 row)<\/span><\/p>\n

-a-<\/p>\n","protected":false},"excerpt":{"rendered":"

Useful and common task, should be well documented. Let\u00b4s try it! Debian 7.4 x64, PostgreSQL 9.3.2<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7,15],"tags":[],"_links":{"self":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/515"}],"collection":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=515"}],"version-history":[{"count":0,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/515\/revisions"}],"wp:attachment":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=515"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=515"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=515"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}