DBA Hub

šŸ“‹Steps in this guide1/6

How to create tablespace in postgres with streaming replication - DBACLASS DBACLASS

FATAL: directory "/pgdata/alm_ts" does not exist HINT: Create this directory for the tablespace before restarting the server.

postgresql configurationintermediate
by PostgreSQL
11 views
1

1. Check streaming replication detail[ RUN ON PRIMARY ]

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
Expanded display is on.
postgres=# <span style="color: #ff0000;"><strong>select * from pg_stat_replication</strong></span>
postgres-# ;
-[ RECORD 1 ]----+---------------------------------
pid              | 6075
usesysid         | 10
usename          | enterprisedb
application_name | walreceiver
<span style="color: #ff0000;"><strong>client_addr      | 10.20.30.41. ---- > This is the hot standby server. 
</strong></span>client_hostname  |
client_port      | 38324
backend_start    | 15-JUN-21 15:05:20.010615 +03:00
backend_xmin     |
<span style="color: #ff0000;"><strong>state            | streaming
</strong></span>sent_lsn         | 0/70001C0
write_lsn        | 0/70001C0
flush_lsn        | 0/70001C0
replay_lsn       | 0/70001C0
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 15-JUN-21 19:00:28.515848 +03:00
2

2. Create tablespace in primary:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Create directory structure

primary$ <span style="color: #ff0000;"><strong>mkdir -p /pgdata/alm_ts</strong></span>

posgres# <span style="color: #ff0000;"><strong>create tablespace alm_ts location '/pgdata/alm_ts';</strong></span>
CREATE TABLESPACE



postgres=#<span style="color: #ff0000;"><strong> \db+</strong></span>
                                       List of tablespaces
    Name    |    Owner     |    Location    | Access privileges | Options |  Size  | Description
------------+--------------+----------------+-------------------+---------+--------+-------------
 pg_default | enterprisedb |                |                   |         | 51 MB  |
 pg_global  | enterprisedb |                |                   |         | 768 kB |
 alm_ts     | enterprisedb | /pgdata/alm_ts |                   |         | 13 MB  |
(3 rows)
3

3 . Check whether standby(replication) server status:

We can see the standby server is down , because the directory structure of the new tablespace is not present.So to fix it just create the same directory structure in standby.

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
-- Run on primary server

postgres=# <span style="color: #ff0000;"><strong>select * from pg_stat_replication
;</strong></span>
(0 rows)


-- Standby server log snippet:

2021-06-15 10:48:23 +03 LOG:  started streaming WAL from primary at 0/5000000 on timeline 1
<em><span style="color: #ff0000;"><strong>2021-06-15 10:59:51 +03 FATAL:  directory "/pgdata/alm_ts" does not exist
2021-06-15 10:59:51 +03 HINT:  Create this directory for the tablespace before restarting the server.
2021-06-15 10:59:51 +03 CONTEXT:  WAL redo at 0/5000520 for Tablespace/CREATE: 16385 "/pgdata/alm_ts"
</strong></span></em>2021-06-15 10:59:51 +03 LOG:  startup process (PID 18171) exited with exit code 1
2021-06-15 10:59:51 +03 LOG:  terminating any other active server processes
2021-06-15 10:59:51 +03 LOG:  <span style="color: #ff0000;"><strong>database system is shut down

</strong></span>
4

4. Create directory structure in standby server

Code/Command (click line numbers to comment):

1
2
-- on standby 
standby$ mkdir -p /pgdata/alm_ts
5

5. Now start the postgres cluster on standby:

The standby server started successfully. > Summary ā€“ > In postgres cluster setup with streaming replication , before creating tablespace on primary, create the tablespace directory structure on standby server . Summary ā€“ > In postgres cluster setup with streaming replication , before creating tablespace on primary, create the tablespace directory structure on standby server .

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- Start the postgres on standby

root# <span style="color: #ff0000;"><strong>systemctl start edb-as-12
</strong></span>
-- Run on standby (
postgres#=# <span style="color: #ff0000;"><strong>SELECT pg_is_in_recovery();</strong></span>
 pg_is_in_recovery
ā”€ā”€ā”€ā”€ā”€ā”€ā”€ā”€ā”€ā”€ā”€ā”€ā”€ā”€ā”€ā”€ā”€ā”€ā”€
 t
(1 row)

Here t means recovery is running fine .

-- check the log

2021-06-15 15:05:19 +03 LOG:  database system was shut down in recovery at 2021-06-15 15:05:14 +03
2021-06-15 15:05:19 +03 LOG:  entering standby mode
2021-06-15 15:05:19 +03 LOG:  redo starts at 0/70000D8
2021-06-15 15:05:19 +03 LOG:  consistent recovery state reached at 0/70001C0
2021-06-15 15:05:19 +03 LOG:  invalid record length at 0/70001C0: wanted 24, got 0
<em><span style="color: #ff0000;"><strong>2021-06-15 15:05:19 +03 LOG:  database system is ready to accept read only connections
</strong></span></em>2021-06-15 15:05:20 +03 LOG:  started streaming WAL from primary at 0/7000000 on timeline 1


--- Run replication status from primary:


postgres# <span style="color: #ff0000;"><strong>\x</strong></span>
postgres=# <span style="color: #ff0000;"><strong>select * from pg_stat_replication</strong></span>
postgres-# ;
-[ RECORD 1 ]----+---------------------------------
pid              | 6075
usesysid         | 10
usename          | enterprisedb
application_name | walreceiver
client_addr      | 10.20.30.41
client_hostname  |
client_port      | 38324
backend_start    | 15-JUN-21 15:08:20.010615 +03:00
backend_xmin     |
<span style="color: #ff0000;">state            | streaming
</span>sent_lsn         | 0/70001C0
write_lsn        | 0/70001C0
flush_lsn        | 0/70001C0
replay_lsn       | 0/70001C0
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 15-JUN-21 19:04:28.515848 +03:00
6

SEE ALSO:

USEFUL POSTGRES SCRIPTS

Comments (0)

Please to add comments

No comments yet. Be the first to comment!