---
# System prepended metadata

title: Postgres recover
tags: [SET]

---

---
tags: SET
---

# Postgres recover

### 1. Stop master

```bash===
sudo systemctl stop postgresql-13
```

### 2. Promote slave(on slave)

```bash===
su - postgres
/usr/pgsql-13/bin/pg_ctl promote -D $PGDATA
```

create slot

```sql
SELECT * FROM pg_create_physical_replication_slot('repl_slot');
SELECT * FROM pg_create_physical_replication_slot('repl_slot1');
SELECT * FROM pg_create_physical_replication_slot('repl_slot2');
```

### 3. Change other slave to connect

Update `primary_conninfo` on `postgresql.conf` and `postgresql.auto.conf` to new master

### 4. Rewind master(on old master)

#### new master

update new master to allow old master to rewind

```sql
GRANT EXECUTE ON function pg_catalog.pg_ls_dir(text, boolean, boolean) TO repluser;
GRANT EXECUTE ON function pg_catalog.pg_stat_file(text, boolean) TO repluser;
GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text) TO repluser;
GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text, bigint, bigint, boolean) TO repluser;
```

add record on `pg_hba.conf` to allow rewind

```vim
host postgres repluser [old master ip]/32 md5
```

#### old master

Update old master postgresql.conf before rewind

```vim===
recovery_target_timeline = 'latest'
primary_slot_name = 'repl_slot'
primary_conninfo = 'user=repluser host=[new master ip] port=5432 sslmode=prefer sslcompression=1'
```

create `standby.signal`

```bash===
touch $PGDATA/standby.signal
```

Then rewind to sync data

```bash= 
/usr/pgsql-13/bin/pg_rewind -D $PGDATA --source-server="user=repluser password=repluser channel_binding=prefer host=[new master] port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any dbname=postgres"
```

Update `primary_conninfo` on `postgresql.conf` and `postgresql.auto.conf` to new master

Then update more config on `postgresql.conf`

```vim
primary_slot_name = 'repl_slot'
recovery_target_timeline = 'latest'
```

create `standby.signal`

```bash===
touch $PGDATA/standby.signal
```

Then start

```bash===
sudo systemctl restart postgresql-13
```

Check logs in directory `$PGDATA/log/*.log` If there are something wrong then go to (5)


### 5. (Optional) Recreate replication from old master to fix invalid state

Stop postgres on old master

```bash===
sudo systemctl stop postgresql-13
```

Clean up data and get base backup

```bash===
su - postgres
cd /var/lib/pgsql/13/data
rm -rf *

pg_basebackup -h [new master] -D . -U repluser -v -P -R -X stream -c fast
```

9. Start postgres

```bash===
sudo systemctl start postgresql-13
```

10. Setup 