Thread: Moving data directory

Moving data directory

From
"Linux Geeks"
Date:
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

Re: Moving data directory

From
"Srinivas Kotapally"
Date:
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


Re: Moving data directory

From
"Linux Geeks"
Date:
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

Re: Moving data directory

From
Peter Eisentraut
Date:
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/

Re: Moving data directory

From
"Srinivas Kotapally"
Date:
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


Re: Moving data directory

From
Ray Stell
Date:
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.

Re: Moving data directory

From
"Linux Geeks"
Date:
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

Re: Moving data directory

From
"Vishal Arora"
Date:
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


Re: Moving data directory

From
"Khan, Mahmood Ahram"
Date:
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

Re: Moving data directory

From
"Vishal Arora"
Date:
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