WALs increase for some reason
0
votes
1
answer
125
views
This is new to me. I have been searching the net but no result.
I have a server, **Server A**, that has been running for a few weeks, it is a new server. I set up FDW for this server to access all tables from a
public
schema from 2 databases (**db B1 and db B2**), lets call it **Server B**. All tables are "stored" on particular schema on server A on 3 different databases (db A1, A2, A3). **There is also streaming replication
between this server and Server C**
Here is the summary of FDW that I do :
- Set up the **Server B** : add entry on pg_hba
to accept all connection, create user fdwuser
, reload the config file
- Set up the **Server A** :
1. Create these schemas on **db A1, A2, A3** to hold the data from **Server B** :
CREATE SCHEMA reference;
CREATE SCHEMA profile;
GRANT USAGE ON SCHEMA profile TO jhon, bon;
GRANT USAGE ON SCHEMA reference TO jhon, bon;
2. Create FDW extension for each databases on db A1, A2, A3:
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
3. Create foreign servers:
CREATE SERVER foreign_profile
FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '10.10.8.40', port '5432', dbname 'db_profile'); `
CREATE SERVER foreign_referensi
FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '10.10.8.40', port '5432', dbname 'db_reference');`
4. Create user mapping:
CREATE USER MAPPING FOR jhon SERVER foreign_reference OPTIONS (user fdwuser, password
'secret');
CREATE USER MAPPING FOR bon SERVER foreign_profile OPTIONS (user fdwuser, password
'secret');
CREATE USER MAPPING FOR jhon SERVER foreign_reference OPTIONS (user fdwuser, password
'secret');
CREATE USER MAPPING FOR bon SERVER foreign_profile OPTIONS (user fdwuser, password
'secret');
CREATE USER MAPPING FOR jhon SERVER foreign_reference OPTIONS (user fdwuser, password
'secret');
CREATE USER MAPPING FOR bon SERVER foreign_profile OPTIONS (user referensi, password
'secret');
5. Import the foreign schemas into database A1, A2, A3:
IMPORT FOREIGN SCHEMA public FROM SERVER foreign_profile INTO profile;
IMPORT FOREIGN SCHEMA public FROM SERVER foreign_referencd INTO reference;
The whole process is a success. **I can access the foreign table on Server B from Server A**. Fine, no problem.
Now, I check the WAL files using SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';
. **It shows some 2100+ files**. This is worrying.
Here is the setting on server A:
archive_command cd .
archive_mode on
checkpoint_completion_target 0.9
checkpoint_flush_after 32
checkpoint_timeout 300
wal_level replica
wal_keep_segments 8
max_wal_senders 10
max_wal_size 8192
hot_standby on
Then I do some checking:
1. select * from pg_catalog.pg_stat_activity
There are 250 entries, dominated by backend_type
= parallel worker
or client backend
2. select * from pg_catalog.pg_stat_archiver
Failed_count = 0;
Archived_count = 71;
3. After a copule of minutes, then I run select * from pg_catalog.pg_stat_archiver
again. It now says :
Failed_count = 0;
Archived_count = 3; -- it seems to be reset for some reasons
4. Check the replication :
select * from pg_catalog.pg_stat_replication
. Results :
state = streaming
sync_state = async
This server seems to be busy I think.
Questions:
1. Why WALs keep on increasing? Is this normal? If not, should I reduce them?
2. Why is the archived_count
reset?
3. What does this command do: archive_command cd .
?
Asked by padjee
(337 rep)
Nov 10, 2022, 06:20 AM
Last activity: Nov 10, 2022, 03:29 PM
Last activity: Nov 10, 2022, 03:29 PM