Thread: Changing default ../data/ directory
I run Slackware (currently -14.2/x86_64) and postgres-11.5. The data directory has always been located in /var/lib/pgsql/<version>/data. This data directory is located in the / partition on a 240G SSD. There's another 2T HDD with a /data partition and I want to both move the current /var/lib/pgsql/data cluster to /data/pgsql/11 and have all future databases use that location. The PGDATA environment variable used to be used for this (but I never did get it satisfactorily working). Web searches show changing initdb or, perhaps, postgresql.conf (there is a /usr/share/postgresql.conf.sample but no postgresql.conf). As this is all new to me I want to learn how to: 1) Define a postgresl.conf and learn where it should be located. 2) Move all current databased in /var/lib/pgsql/11/data to /data/pgsql/11. 3) Create all new databases in /data/pgsql/11 by default. I'm sure it's in the manual yet I'm unsure just where to start reading. Rich
Hi Rich Shepard,
Step 1-: Stop the postgres services and change the path of the data directory in postgres.conf file.
Step 2-: rsync the flie to new path. And start the service.
Step 3-: connect to postgres and check command output from psql shell SHOW data_directory; should see now data path.
On Sat, 4 Jan 2020, 8:50 pm Rich Shepard, <rshepard@appl-ecosys.com> wrote:
I run Slackware (currently -14.2/x86_64) and postgres-11.5. The data
directory has always been located in /var/lib/pgsql/<version>/data. This
data directory is located in the / partition on a 240G SSD.
There's another 2T HDD with a /data partition and I want to both move the
current /var/lib/pgsql/data cluster to /data/pgsql/11 and have all future
databases use that location.
The PGDATA environment variable used to be used for this (but I never did
get it satisfactorily working). Web searches show changing initdb or,
perhaps, postgresql.conf (there is a /usr/share/postgresql.conf.sample but
no postgresql.conf).
As this is all new to me I want to learn how to:
1) Define a postgresl.conf and learn where it should be located.
2) Move all current databased in /var/lib/pgsql/11/data to /data/pgsql/11.
3) Create all new databases in /data/pgsql/11 by default.
I'm sure it's in the manual yet I'm unsure just where to start reading.
Rich
On 1/4/20 7:20 AM, Rich Shepard wrote: > I run Slackware (currently -14.2/x86_64) and postgres-11.5. The data > directory has always been located in /var/lib/pgsql/<version>/data. This > data directory is located in the / partition on a 240G SSD. > > There's another 2T HDD with a /data partition and I want to both move the > current /var/lib/pgsql/data cluster to /data/pgsql/11 and have all future > databases use that location. > > The PGDATA environment variable used to be used for this (but I never did > get it satisfactorily working). Web searches show changing initdb or, > perhaps, postgresql.conf (there is a /usr/share/postgresql.conf.sample but > no postgresql.conf). Not following above: 1) Are you looking for your current postgresql.conf? 2) Some examples of postgresql.conf? More below. > > As this is all new to me I want to learn how to: > > 1) Define a postgresl.conf and learn where it should be located. > > 2) Move all current databased in /var/lib/pgsql/11/data to /data/pgsql/11. > > 3) Create all new databases in /data/pgsql/11 by default. > > I'm sure it's in the manual yet I'm unsure just where to start reading. > > Rich > > https://www.postgresql.org/docs/11/runtime-config-file-locations.html#GUC-DATA-DIRECTORY -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, 4 Jan 2020, nikhil raj wrote: > Step 1-: Stop the postgres services and change the path of the > data directory in postgres.conf file. The postgresql.conf.sample notes that the default value of data_directory is taken from -D in the startup command or the PGDATA environment variable. I suppose that I can define PGDATA in ~/.bash_profile as I'm the only user. Thanks, Rich
On Sat, 4 Jan 2020, Adrian Klaver wrote: > Not following above: > 1) Are you looking for your current postgresql.conf? Yes. > More below. > https://www.postgresql.org/docs/11/runtime-config-file-locations.html#GUC-DATA-DIRECTORY Thanks, Adrian. Rich
On Sat, Jan 4, 2020 at 10:20 AM Rich Shepard <rshepard@appl-ecosys.com> wrote:
The PGDATA environment variable used to be used for this (but I never did
get it satisfactorily working).
PGDATA should work fine if you always start the server directly. But if you sudo or su to another user, they likely won't inherit your environment variables. And if you use some kind of start-up script, they will likely override it. How do you start and stop PostgreSQL?
I'm sure it's in the manual yet I'm unsure just where to start reading.
If you are using start-up scripts that come with some installation package, then the details are up to the packager. The PostgreSQL manual is of little use in knowing what the packager decided to do. It will describe what the pieces are, but not how they got assembled together.
Cheers,
Jeff
On Sat, 4 Jan 2020 07:20:44 -0800 (PST), Rich Shepard <rshepard@appl-ecosys.com> wrote: >As this is all new to me I want to learn how to: > >1) Define a postgresl.conf and learn where it should be located. > >2) Move all current databased in /var/lib/pgsql/11/data to /data/pgsql/11. > >3) Create all new databases in /data/pgsql/11 by default. > >I'm sure it's in the manual yet I'm unsure just where to start reading. > >Rich Since the new drive is local you can just move the data directory to its new location and link to it from the default (/var) location. No configuration changes needed. Won't help you learn necessarily, but solves the problem. YMMV, George
On Sat, 4 Jan 2020, Jeff Janes wrote: > PGDATA should work fine if you always start the server directly. But if > you sudo or su to another user, they likely won't inherit your environment > variables. And if you use some kind of start-up script, they will likely > override it. How do you start and stop PostgreSQL? Jeff, Slackware's /etc/rc.d/rc.postgresql start command. I can modify that script to change the data directory path. Thanks, Rich
On Sat, 4 Jan 2020, George Neuner wrote: > Since the new drive is local you can just move the data directory to its > new location and link to it from the default (/var) location. No > configuration changes needed. George, Huh! It didn't occur to me to make a softlink to the new directory from the old one. That looks like the most parsimonious solution. Thanks, Rich
On 1/4/20 8:27 AM, Rich Shepard wrote: > On Sat, 4 Jan 2020, Adrian Klaver wrote: > >> Not following above: >> 1) Are you looking for your current postgresql.conf? > > Yes I don't see anything here: https://slackbuilds.org/slackbuilds/14.2/system/postgresql/rc.postgresql.new that changes the conf location, so postgresql.conf should be in the DATADIR: /var/lib/pgsql/$PG_VERSION/data > >> More below. >> https://www.postgresql.org/docs/11/runtime-config-file-locations.html#GUC-DATA-DIRECTORY >> > > Thanks, Adrian. > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, 4 Jan 2020, Adrian Klaver wrote: > I don't see anything here: > https://slackbuilds.org/slackbuilds/14.2/system/postgresql/rc.postgresql.new > that changes the conf location, so postgresql.conf should be in the DATADIR: > /var/lib/pgsql/$PG_VERSION/data Adrian, How interesting. When I used 'locate postgresql.conf' it did not find it, only /usr/share/postgresql-11/postgresql.conf.sample. That's because users are not allowed to see what's in /var/lib/. I don't recall running into this issue before. Thanks, Rich