Thread: Moving data directory
Hi All, I'm very new to postgresql and would like to move my data directory to another partition in Ubuntu. The standard installation is in /usr/lib/postgresql. How do I move it into /var/lib/postgresql. Regards, Dino -- To Analyze Is To Know Batte Field, Differ from Battle Itself
You can do an initdb -d /var/lib/postgresql to initialize the data directory in
the /var/lib/postgresql directory. If you also have data in the
/usr/lib/postgresql - make sure you back it up and restore it in the new
directory.
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]
On Behalf Of Linux Geeks
Sent: Saturday, May 12, 2007 12:48 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Moving data directory
Hi All,
I'm very new to postgresql and would like to move my data directory to another
partition in Ubuntu. The standard installation is in /usr/lib/postgresql. How do
I move it into /var/lib/postgresql.
Regards,
Dino
--
To Analyze Is To Know Batte Field, Differ from Battle Itself
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Hi Srinivas, Is it permanently change it ? Do I have to change some entry in configuration file ? Regards, Dino On 5/12/07, Srinivas Kotapally <ksrinivas@coriendo.com> wrote: > You can do an initdb -d /var/lib/postgresql to initialize the data directory in > the /var/lib/postgresql directory. If you also have data in the > /usr/lib/postgresql - make sure you back it up and restore it in the new > directory. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > -- To Analyze Is To Know Batte Field, Differ from Battle Itself
Linux Geeks wrote: > I'm very new to postgresql and would like to move my data directory > to another partition in Ubuntu. The standard installation is in > /usr/lib/postgresql. How do I move it into /var/lib/postgresql. The standard data directory on Ubuntu *is* /var/lib/postgresql. You must be confusing something. -- Peter Eisentraut http://developer.postgresql.org/~petere/
No this will permanently change it. Check out the documentation: http://www.postgresql.org/docs/8.2/interactive/server-start.html http://www.postgresql.org/docs/8.2/interactive/app-initdb.html Cheers Srinivas -----Original Message----- From: Linux Geeks [mailto:indolinux.geeks@gmail.com] Sent: Saturday, May 12, 2007 1:58 PM To: ksrinivas@coriendo.com; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Moving data directory Hi Srinivas, Is it permanently change it ? Do I have to change some entry in configuration file ? Regards, Dino On 5/12/07, Srinivas Kotapally <ksrinivas@coriendo.com> wrote: > You can do an initdb -d /var/lib/postgresql to initialize the data > directory in the /var/lib/postgresql directory. If you also have data > in the /usr/lib/postgresql - make sure you back it up and restore it > in the new directory. > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > -- To Analyze Is To Know Batte Field, Differ from Battle Itself
On Sat, May 12, 2007 at 11:47:46PM +0700, Linux Geeks wrote:
> I'm very new to postgresql and would like to move my data directory to
> another partition in Ubuntu.
I don't think there is anything internal to pg that locks the data
location. Just tell postmaster where you move it to. Shut down the
postmaster and move it and start it with the -D flag pointing to the
new location:
$ pg_ctl start -D /var/pgsql/data
server starting
$ psql -p 5437 testdb jira
Password for user jira:
Welcome to psql 8.2.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
testdb=> \q
pg_ctl stop -D /var/pgsql/data
waiting for server to shut down....,7222,,2007-05-13 19:52:06.675 EDT,,1,,, LOG: logger shutting down
done
server stopped
$ mv /var/pgsql/data-move-test /var/pgsql/data
$ pg_ctl start -D /var/pgsql/data-move-test
server starting
$ psql -p 5437 testdb jira
Password for user jira:
Welcome to psql 8.2.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
testdb=> \q
pg_ctl stop -D /var/pgsql/data-move-test
waiting for server to shut down....,7245,,2007-05-13 19:55:00.944 EDT,,1,,, LOG: logger shutting down
done
server stopped
$ mv /var/pgsql/data-move-test /var/pgsql/data
$ pg_ctl start -D /var/pgsql/data
server starting
$ psql -p 5437 testdb jira
Password for user jira:
Welcome to psql 8.2.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
testdb=> \q
--
Lost time is when we learn nothing from the experiences of life. Time
gained is when we grow to have a wisdom that is tested in the reality of life.
Hi Ray, Sorry for late reply. Yes, I have tested your instructions and working well. The think is now I need to configure so that it will always automically start with the new location. Thank you very much for the detailed instructions, really appreciated it. Regards, Dino On 5/14/07, Ray Stell <stellr@cns.vt.edu> wrote: > On Sat, May 12, 2007 at 11:47:46PM +0700, Linux Geeks wrote: > > I'm very new to postgresql and would like to move my data directory to > > another partition in Ubuntu. > > > I don't think there is anything internal to pg that locks the data > location. Just tell postmaster where you move it to. Shut down the > postmaster and move it and start it with the -D flag pointing to the > new location: > > $ pg_ctl start -D /var/pgsql/data > server starting > > $ psql -p 5437 testdb jira > Password for user jira: > Welcome to psql 8.2.3, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help with psql commands > \g or terminate with semicolon to execute query > \q to quit > > testdb=> \q > > pg_ctl stop -D /var/pgsql/data > waiting for server to shut down....,7222,,2007-05-13 19:52:06.675 EDT,,1,,, LOG: logger shutting down > done > server stopped > > $ mv /var/pgsql/data-move-test /var/pgsql/data > > $ pg_ctl start -D /var/pgsql/data-move-test > server starting > > $ psql -p 5437 testdb jira > Password for user jira: > Welcome to psql 8.2.3, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help with psql commands > \g or terminate with semicolon to execute query > \q to quit > > testdb=> \q > > pg_ctl stop -D /var/pgsql/data-move-test > waiting for server to shut down....,7245,,2007-05-13 19:55:00.944 EDT,,1,,, LOG: logger shutting down > done > server stopped > > $ mv /var/pgsql/data-move-test /var/pgsql/data > > $ pg_ctl start -D /var/pgsql/data > server starting > > $ psql -p 5437 testdb jira > Password for user jira: > Welcome to psql 8.2.3, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help with psql commands > \g or terminate with semicolon to execute query > \q to quit > > testdb=> \q > > -- > Lost time is when we learn nothing from the experiences of life. Time > gained is when we grow to have a wisdom that is tested in the reality of life. > -- To Analyze Is To Know Batte Field, Differ from Battle Itself
Each time you start postgres server using pg_ctl you have to mention the data directory location using -D option. Alternatively you can specify in the data_directory parameter of postgresql.conf file. - vishal >From: "Linux Geeks" <indolinux.geeks@gmail.com> >To: "Ray Stell" <stellr@cns.vt.edu> >CC: pgsql-admin@postgresql.org >Subject: Re: [ADMIN] Moving data directory >Date: Tue, 22 May 2007 10:45:17 +0700 > >Hi Ray, > >Sorry for late reply. Yes, I have tested your instructions and working >well. The think is now I need to configure so that it will always >automically start with the new location. > >Thank you very much for the detailed instructions, really appreciated it. > >Regards, > >Dino > >On 5/14/07, Ray Stell <stellr@cns.vt.edu> wrote: >>On Sat, May 12, 2007 at 11:47:46PM +0700, Linux Geeks wrote: >> > I'm very new to postgresql and would like to move my data directory to >> > another partition in Ubuntu. >> >> >>I don't think there is anything internal to pg that locks the data >>location. Just tell postmaster where you move it to. Shut down the >>postmaster and move it and start it with the -D flag pointing to the >>new location: >> >>$ pg_ctl start -D /var/pgsql/data >>server starting >> >>$ psql -p 5437 testdb jira >>Password for user jira: >>Welcome to psql 8.2.3, the PostgreSQL interactive terminal. >> >>Type: \copyright for distribution terms >> \h for help with SQL commands >> \? for help with psql commands >> \g or terminate with semicolon to execute query >> \q to quit >> >>testdb=> \q >> >>pg_ctl stop -D /var/pgsql/data >>waiting for server to shut down....,7222,,2007-05-13 19:52:06.675 >>EDT,,1,,, LOG: logger shutting down >> done >>server stopped >> >>$ mv /var/pgsql/data-move-test /var/pgsql/data >> >>$ pg_ctl start -D /var/pgsql/data-move-test >>server starting >> >>$ psql -p 5437 testdb jira >>Password for user jira: >>Welcome to psql 8.2.3, the PostgreSQL interactive terminal. >> >>Type: \copyright for distribution terms >> \h for help with SQL commands >> \? for help with psql commands >> \g or terminate with semicolon to execute query >> \q to quit >> >>testdb=> \q >> >>pg_ctl stop -D /var/pgsql/data-move-test >>waiting for server to shut down....,7245,,2007-05-13 19:55:00.944 >>EDT,,1,,, LOG: logger shutting down >> done >>server stopped >> >>$ mv /var/pgsql/data-move-test /var/pgsql/data >> >>$ pg_ctl start -D /var/pgsql/data >>server starting >> >>$ psql -p 5437 testdb jira >>Password for user jira: >>Welcome to psql 8.2.3, the PostgreSQL interactive terminal. >> >>Type: \copyright for distribution terms >> \h for help with SQL commands >> \? for help with psql commands >> \g or terminate with semicolon to execute query >> \q to quit >> >>testdb=> \q >> >>-- >> Lost time is when we learn nothing from the experiences of life. Time >> gained is when we grow to have a wisdom that is tested in the reality of >>life. >> > > >-- >To Analyze Is To Know Batte Field, Differ from Battle Itself > >---------------------------(end of broadcast)--------------------------- >TIP 5: don't forget to increase your free space map settings _________________________________________________________________ More photos, more messages, more storage�get 2GB with Windows Live Hotmail. http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_2G_0507
No needs to specify in pg_ctl start/stop clause instead add a variable in PATH. PG_DATA = d:\postgresdata\data. It is start by just typing pg_ctl start/stop. Thanks & Best Regards M.AHRAM KHAN -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Vishal Arora Sent: Tuesday, May 22, 2007 10:09 AM To: indolinux.geeks@gmail.com; stellr@cns.vt.edu Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Moving data directory Each time you start postgres server using pg_ctl you have to mention the data directory location using -D option. Alternatively you can specify in the data_directory parameter of postgresql.conf file. - vishal >From: "Linux Geeks" <indolinux.geeks@gmail.com> >To: "Ray Stell" <stellr@cns.vt.edu> >CC: pgsql-admin@postgresql.org >Subject: Re: [ADMIN] Moving data directory >Date: Tue, 22 May 2007 10:45:17 +0700 > >Hi Ray, > >Sorry for late reply. Yes, I have tested your instructions and working >well. The think is now I need to configure so that it will always >automically start with the new location. > >Thank you very much for the detailed instructions, really appreciated it. > >Regards, > >Dino > >On 5/14/07, Ray Stell <stellr@cns.vt.edu> wrote: >>On Sat, May 12, 2007 at 11:47:46PM +0700, Linux Geeks wrote: >> > I'm very new to postgresql and would like to move my data directory to >> > another partition in Ubuntu. >> >> >>I don't think there is anything internal to pg that locks the data >>location. Just tell postmaster where you move it to. Shut down the >>postmaster and move it and start it with the -D flag pointing to the >>new location: >> >>$ pg_ctl start -D /var/pgsql/data >>server starting >> >>$ psql -p 5437 testdb jira >>Password for user jira: >>Welcome to psql 8.2.3, the PostgreSQL interactive terminal. >> >>Type: \copyright for distribution terms >> \h for help with SQL commands >> \? for help with psql commands >> \g or terminate with semicolon to execute query >> \q to quit >> >>testdb=> \q >> >>pg_ctl stop -D /var/pgsql/data >>waiting for server to shut down....,7222,,2007-05-13 19:52:06.675 >>EDT,,1,,, LOG: logger shutting down >> done >>server stopped >> >>$ mv /var/pgsql/data-move-test /var/pgsql/data >> >>$ pg_ctl start -D /var/pgsql/data-move-test >>server starting >> >>$ psql -p 5437 testdb jira >>Password for user jira: >>Welcome to psql 8.2.3, the PostgreSQL interactive terminal. >> >>Type: \copyright for distribution terms >> \h for help with SQL commands >> \? for help with psql commands >> \g or terminate with semicolon to execute query >> \q to quit >> >>testdb=> \q >> >>pg_ctl stop -D /var/pgsql/data-move-test >>waiting for server to shut down....,7245,,2007-05-13 19:55:00.944 >>EDT,,1,,, LOG: logger shutting down >> done >>server stopped >> >>$ mv /var/pgsql/data-move-test /var/pgsql/data >> >>$ pg_ctl start -D /var/pgsql/data >>server starting >> >>$ psql -p 5437 testdb jira >>Password for user jira: >>Welcome to psql 8.2.3, the PostgreSQL interactive terminal. >> >>Type: \copyright for distribution terms >> \h for help with SQL commands >> \? for help with psql commands >> \g or terminate with semicolon to execute query >> \q to quit >> >>testdb=> \q >> >>-- >> Lost time is when we learn nothing from the experiences of life. Time >> gained is when we grow to have a wisdom that is tested in the reality of >>life. >> > > >-- >To Analyze Is To Know Batte Field, Differ from Battle Itself > >---------------------------(end of broadcast)--------------------------- >TIP 5: don't forget to increase your free space map settings _________________________________________________________________ More photos, more messages, more storage-get 2GB with Windows Live Hotmail. http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migr ation_HM_mini_2G_0507 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
This is also an alternative that can be used. I am not sure about windows, but it is PGDATA variable in Linux. it can be set using export command. - vishal >From: "Khan, Mahmood Ahram" <Mahmood.Khan@in.unisys.com> >To: "Vishal Arora" ><aroravishal22@hotmail.com>,<indolinux.geeks@gmail.com>,<stellr@cns.vt.edu> >CC: <pgsql-admin@postgresql.org> >Subject: Re: [ADMIN] Moving data directory >Date: Tue, 22 May 2007 11:11:00 +0530 > >No needs to specify in pg_ctl start/stop clause instead add a variable >in PATH. PG_DATA = d:\postgresdata\data. It is start by just typing >pg_ctl start/stop. >Thanks & Best Regards > >M.AHRAM KHAN > >-----Original Message----- >From: pgsql-admin-owner@postgresql.org >[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Vishal Arora >Sent: Tuesday, May 22, 2007 10:09 AM >To: indolinux.geeks@gmail.com; stellr@cns.vt.edu >Cc: pgsql-admin@postgresql.org >Subject: Re: [ADMIN] Moving data directory > >Each time you start postgres server using pg_ctl you have to mention the > >data directory location using -D option. Alternatively you can specify >in >the data_directory parameter of postgresql.conf file. > >- vishal > > > >From: "Linux Geeks" <indolinux.geeks@gmail.com> > >To: "Ray Stell" <stellr@cns.vt.edu> > >CC: pgsql-admin@postgresql.org > >Subject: Re: [ADMIN] Moving data directory > >Date: Tue, 22 May 2007 10:45:17 +0700 > > > >Hi Ray, > > > >Sorry for late reply. Yes, I have tested your instructions and working > >well. The think is now I need to configure so that it will always > >automically start with the new location. > > > >Thank you very much for the detailed instructions, really appreciated >it. > > > >Regards, > > > >Dino > > > >On 5/14/07, Ray Stell <stellr@cns.vt.edu> wrote: > >>On Sat, May 12, 2007 at 11:47:46PM +0700, Linux Geeks wrote: > >> > I'm very new to postgresql and would like to move my data directory >to > >> > another partition in Ubuntu. > >> > >> > >>I don't think there is anything internal to pg that locks the data > >>location. Just tell postmaster where you move it to. Shut down the > >>postmaster and move it and start it with the -D flag pointing to the > >>new location: > >> > >>$ pg_ctl start -D /var/pgsql/data > >>server starting > >> > >>$ psql -p 5437 testdb jira > >>Password for user jira: > >>Welcome to psql 8.2.3, the PostgreSQL interactive terminal. > >> > >>Type: \copyright for distribution terms > >> \h for help with SQL commands > >> \? for help with psql commands > >> \g or terminate with semicolon to execute query > >> \q to quit > >> > >>testdb=> \q > >> > >>pg_ctl stop -D /var/pgsql/data > >>waiting for server to shut down....,7222,,2007-05-13 19:52:06.675 > >>EDT,,1,,, LOG: logger shutting down > >> done > >>server stopped > >> > >>$ mv /var/pgsql/data-move-test /var/pgsql/data > >> > >>$ pg_ctl start -D /var/pgsql/data-move-test > >>server starting > >> > >>$ psql -p 5437 testdb jira > >>Password for user jira: > >>Welcome to psql 8.2.3, the PostgreSQL interactive terminal. > >> > >>Type: \copyright for distribution terms > >> \h for help with SQL commands > >> \? for help with psql commands > >> \g or terminate with semicolon to execute query > >> \q to quit > >> > >>testdb=> \q > >> > >>pg_ctl stop -D /var/pgsql/data-move-test > >>waiting for server to shut down....,7245,,2007-05-13 19:55:00.944 > >>EDT,,1,,, LOG: logger shutting down > >> done > >>server stopped > >> > >>$ mv /var/pgsql/data-move-test /var/pgsql/data > >> > >>$ pg_ctl start -D /var/pgsql/data > >>server starting > >> > >>$ psql -p 5437 testdb jira > >>Password for user jira: > >>Welcome to psql 8.2.3, the PostgreSQL interactive terminal. > >> > >>Type: \copyright for distribution terms > >> \h for help with SQL commands > >> \? for help with psql commands > >> \g or terminate with semicolon to execute query > >> \q to quit > >> > >>testdb=> \q > >> > >>-- > >> Lost time is when we learn nothing from the experiences of life. >Time > >> gained is when we grow to have a wisdom that is tested in the >reality of > >>life. > >> > > > > > >-- > >To Analyze Is To Know Batte Field, Differ from Battle Itself > > > >---------------------------(end of >broadcast)--------------------------- > >TIP 5: don't forget to increase your free space map settings > >_________________________________________________________________ >More photos, more messages, more storage-get 2GB with Windows Live >Hotmail. >http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migr >ation_HM_mini_2G_0507 > > >---------------------------(end of broadcast)--------------------------- >TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > >---------------------------(end of broadcast)--------------------------- >TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq _________________________________________________________________ Catch suspicious messages before you open them�with Windows Live Hotmail. http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_protection_0507