WALs increase for some reason

Question

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 .?

Answer 1

Importing foreign schemas needs to generate WAL, as it needs to enter the descriptions of those tables into the system catalogs. But it should not generate huge amounts of WAL, unless those foreign schemas have a huge number of tables (is is the number of tables and columns that matter, not the number of rows in the tables. Because only the description of the tables gets recorded, not the contents).

So most likely, FDW has nothing to do with this. It is something else which is generating that WAL, and something else again (probably replication slots) which is causing the WAL to be retained.

You can run pg_waldump on the retained WAL to try to figure out what is causing so much of it. This is not easy to interpret, so it would probably be best to just think about what your system is doing other than FDW to come up with theories first.