DBA Hub

📋Steps in this guide1/9

How to change postgres data directory - DBACLASS DBACLASS

Below article explains how to change postgres data directory to a new location.Update PGDATA inside service file and restart the pg service

postgresql configurationintermediate
by PostgreSQL
12 views
1

1. Check existing data directory

Code/Command (click line numbers to comment):

1
2
3
4
5
postgres=# <span style="color: #ff0000;"><strong>show data_directory;</strong></span>
     data_directory
------------------------
 /var/lib/edb/as11/data
(1 row)
2

2. Create new directory structure

> Note – if 600 permission is not provided to the directory, then PG cluster service will fail with below error. Active: failed (Result: exit-code) since Sun 2021-06-13 06:46:52 +03; 8s ago Process: 761 ExecStart=/usr/edb/as11/bin/edb-postmaster -D ${PGDATA} (code=exited, status=1/FAILURE) Process: 753 ExecStartPre=/usr/edb/as11/bin/edb-as-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS) Main PID: 761 (code=exited, status=1/FAILURE) Jun 13 06:46:52 scdvlbss14 systemd[1]: Starting EDB Postgres Advanced Server 11… Jun 13 06:46:52 scdvlbss14 edb-postmaster[761]: 2021-06-13 06:46:52 +03 FATAL: data directory “/oradata/pgdata” has invalid permissions Jun 13 06:46:52 scdvlbss14 edb-postmaster[761]: 2021-06-13 06:46:52 +03 DETAIL: Permissions should be u=rwx (0700) or u=rwx,g=rx (0750). Jun 13 06:46:52 scdvlbss14 systemd[1]: edb-as-11.service: main process exited, code=exited, status=1/FAILURE Jun 13 06:46:52 scdvlbss14 systemd[1]: Failed to start EDB Postgres Advanced Server 11. Jun 13 06:46:52 scdvlbss14 systemd[1]: Unit edb-as-11.service entered failed state. Jun 13 06:46:52 scdvlbss14 systemd[1]: edb-as-11.service failed. Note – if 600 permission is not provided to the directory, then PG cluster service will fail with below error. Active: failed (Result: exit-code) since Sun 2021-06-13 06:46:52 +03; 8s ago Process: 761 ExecStart=/usr/edb/as11/bin/edb-postmaster -D ${PGDATA} (code=exited, status=1/FAILURE) Process: 753 ExecStartPre=/usr/edb/as11/bin/edb-as-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS) Main PID: 761 (code=exited, status=1/FAILURE) Jun 13 06:46:52 scdvlbss14 systemd[1]: Starting EDB Postgres Advanced Server 11… Jun 13 06:46:52 scdvlbss14 edb-postmaster[761]: 2021-06-13 06:46:52 +03 FATAL: data directory “/oradata/pgdata” has invalid permissions Jun 13 06:46:52 scdvlbss14 edb-postmaster[761]: 2021-06-13 06:46:52 +03 DETAIL: Permissions should be u=rwx (0700) or u=rwx,g=rx (0750). Jun 13 06:46:52 scdvlbss14 systemd[1]: edb-as-11.service: main process exited, code=exited, status=1/FAILURE Jun 13 06:46:52 scdvlbss14 systemd[1]: Failed to start EDB Postgres Advanced Server 11. Jun 13 06:46:52 scdvlbss14 systemd[1]: Unit edb-as-11.service entered failed state. Jun 13 06:46:52 scdvlbss14 systemd[1]: edb-as-11.service failed.

Code/Command (click line numbers to comment):

1
2
-bash-4.2$ mkdir -p /oradata/pgdata
-bash-4.2$ chmod 0700 /oradata/pgdata
3

3. Stop postgres service

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-bash-4.2$ <span style="color: #ff0000;"><strong>systemctl status edb-as-11</strong></span>
● edb-as-11.service - EDB Postgres Advanced Server 11
   Loaded: loaded (<span style="color: #ff0000;"><strong>/usr/lib/systemd/system/edb-as-11.service</strong></span>; disabled; vendor preset: disabled)
   Active: active (running) since Mon 2020-12-28 11:22:01 +03; 5 months 14 days ago
 Main PID: 30787 (edb-postmaster)
    Tasks: 10
   Memory: 8.2G
   CGroup: /system.slice/edb-as-11.service
           ├─25828 postgres: checkpointer
           ├─25829 postgres: background writer
           ├─25831 postgres: walwriter
           ├─25832 postgres: autovacuum launcher
           ├─25835 postgres: archiver   last was 0000000100000010000000C8
           ├─25836 postgres: stats collector
           ├─25837 postgres: dbms_aq launcher
           ├─25838 postgres: logical replication launcher
           ├─30787 /usr/edb/as11/bin/edb-postmaster -D /var/lib/edb/as11/data
           └─30790 postgres: logger
-bash-4.2$

-- Run stop command
-bash-4.2$ <span style="color: #ff0000;"><strong>systemctl stop edb-as-11


</strong></span>
4

4 . Copy the data directory to new location:

Code/Command (click line numbers to comment):

1
2
3
-bash-4.2$ cd  /var/lib/edb/as11/data
-bash-4.2$ cp -r * /oradata/pgdata/
-bash-4.2$ ls -ltr   /oradata/pgdata/
5

5. Update postgres service file:[ as root user ]

It is not advisable to edit service file from default location. So copy it to /etc/systemd/system location and edit it there. In our environment, data_directory, hba_file, ident_file parameters were commented in postgres.conf file, i.e the values are getting picked from service file. So we are not doing any change to postgres.conf file. So Please crosscheck postgres.conf file, If these parameters were defined and uncommented, then you need to update the path in this file also.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[root]#  <span style="color: #ff0000;"><strong>cp /usr/lib/systemd/system/edb-as-11.service /etc/systemd/system/</strong></span>
[root]# cd /etc/systemd/system/
[rootsystem]# ls -ltr edb*
-rw-r--r-- 1 root root 1396 Jun 13 06:41 edb-as-11.service

-- Edit below path, pointing to new data_directory 

[root@scdvlbss14 system]# vi edb-as-11.service


# Location of database directory
Environment=PGDATA=/oradata/pgdata
PIDFile=/oradata/pgdata/postmaster.pid

# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'           # use data in another directory
                                        # (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf'     # host-based authentication file
                                        # (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
                                        # (change requires restart)
6

6 . Reload the systemctl daemon:

Code/Command (click line numbers to comment):

1
2
3
4
[root#]
<span style="color: #ff0000;">systemctl daemon-reload

</span>
7

7. Start the postgres service:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[root]# <span style="color: #ff0000;"><strong>systemctl start edb-as-11</strong></span>

-- Check status:

[root]# systemctl status edb-as-11
● edb-as-11.service - EDB Postgres Advanced Server 11
   Loaded: loaded (<span style="color: #ff0000;"><strong>/etc/systemd/system/edb-as-11.service</strong></span>; disabled; vendor preset: disabled)
   Active: active (running) since Sun 2021-06-13 06:47:34 +03; 6s ago
  Process: 1741 ExecStartPre=/usr/edb/as11/bin/edb-as-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 1748 (edb-postmaster)
    Tasks: 10
   Memory: 202.8M
   CGroup: /system.slice/edb-as-11.service
           ├─1748 /usr/edb/as11/bin/edb-postmaster -D /oradata/pgdata
           ├─1751 postgres: logger
           ├─1753 postgres: checkpointer
           ├─1754 postgres: background writer
           ├─1755 postgres: walwriter
           ├─1756 postgres: autovacuum launcher
           ├─1757 postgres: archiver
           ├─1758 postgres: stats collector
           ├─1759 postgres: dbms_aq launcher
           └─1760 postgres: logical replication launcher

Jun 13 06:47:33 dbhost1 systemd[1]: Starting EDB Postgres Advanced Server 11...
Jun 13 06:47:33 dbhost1 edb-postmaster[1748]: 2021-06-13 06:47:33 +03 LOG:  listening on IPv4 address "0.0.0.0", port 5435
Jun 13 06:47:33 dbhost1 edb-postmaster[1748]: 2021-06-13 06:47:33 +03 LOG:  listening on IPv6 address "::", port 5435
Jun 13 06:47:33 dbhost1 edb-postmaster[1748]: 2021-06-13 06:47:33 +03 LOG:  listening on Unix socket "/tmp/.s.PGSQL.5435"
Jun 13 06:47:34 dbhost1 edb-postmaster[1748]: 2021-06-13 06:47:34 +03 LOG:  redirecting log output to logging collector process
Jun 13 06:47:34 dbhost1 edb-postmaster[1748]: 2021-06-13 06:47:34 +03 HINT:  Future log output will appear in directory "log".
Jun 13 06:47:34 dbhosg1 systemd[1]: Started EDB Postgres Advanced Server 11.
8

8. Check the data_directory value:

We have successfully changed the data_directory.

Code/Command (click line numbers to comment):

1
2
3
4
5
postgres=# show data_directory;
 data_directory
-----------------
 /oradata/pgdata
(1 row)
9

SEE ALSO:

- How To Move A Tablespace To New Directory In Postgres - How To Make A Postgres Database Readonly - How To Change Port Number In Postgres - How To Access Csv Files On File System Using File_fdw - How To Setup Streaming Replication In Postgres - EDB Failover Manager (EFM) For Managing Streaming Replication

Comments (0)

Please to add comments

No comments yet. Be the first to comment!