Sample Header Ad - 728x90

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