Обсуждение: 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

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, 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