Thread: Changing default ../data/ directory

Changing default ../data/ directory

From
Rich Shepard
Date:
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



Re: Changing default ../data/ directory

From
nikhil raj
Date:
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


Re: Changing default ../data/ directory

From
Adrian Klaver
Date:
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



Re: Changing default ../data/ directory

From
Rich Shepard
Date:
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



Re: Changing default ../data/ directory

From
Rich Shepard
Date:
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



Re: Changing default ../data/ directory

From
Jeff Janes
Date:
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

Re: Changing default ../data/ directory

From
George Neuner
Date:
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




Re: Changing default ../data/ directory

From
Rich Shepard
Date:
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



Re: Changing default ../data/ directory

From
Rich Shepard
Date:
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



Re: Changing default ../data/ directory

From
Adrian Klaver
Date:
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



Re: Changing default ../data/ directory

From
Rich Shepard
Date:
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