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:

  3. Create foreign servers:

    CREATE SERVER foreign_profile 
      FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '', port '5432', dbname 'db_profile'); ` 
    CREATE SERVER foreign_referensi 
      FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host     '', port '5432', dbname 'db_reference');` 
  4. Create user mapping:

    CREATE USER MAPPING FOR jhon SERVER foreign_reference OPTIONS (user fdwuser, password        
    CREATE USER MAPPING FOR bon SERVER foreign_profile OPTIONS (user fdwuser, password 
    CREATE USER MAPPING FOR jhon SERVER foreign_reference OPTIONS (user fdwuser, password        
    CREATE USER MAPPING FOR bon SERVER foreign_profile OPTIONS (user fdwuser, password 
    CREATE USER MAPPING FOR jhon SERVER foreign_reference OPTIONS (user fdwuser, password 
    CREATE USER MAPPING FOR bon SERVER foreign_profile OPTIONS (user referensi, password 
  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.


  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 .?
  • Why did you turn on archive mode? And why did you turn it on without configuring a proper archive command?
    – user1822
    Nov 10, 2022 at 6:38
  • not sure why turning the archive mode without the proper archive command. I guess the other DBA does not want to reset the server. From what I read, turning the archive_command = cd . is a hacky workaround :[dba.stackexchange.com/questions/77626/…. I do not know the reason, but the server has been working well. Seems like all the archived WALs vanish into thin air. The only thing that is unwell is the number of WALs
    – padjee
    Nov 10, 2022 at 6:51
  • 2
    Nevertheless, that has nothing to do with your FDWs they don't change who much WAL is generated or kept (as e.g. logical replication)
    – user1822
    Nov 10, 2022 at 6:58
  • yes @a_horse_with_no_name . you are right. I just checked, there is streaming replication going on as well alongside FDW. so definitely the replication has something to do with the WALs.
    – padjee
    Nov 10, 2022 at 7:02
  • 1
    Probably a stale replication slot. Look into pg_replication_slots for entries where active is false. Drop those replication slots and wait for the next checkpoint. Nov 10, 2022 at 7:05

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

  • How about the archive_count that reset? What caused it to reset? On another server, the counter keeps on going.
    – padjee
    Nov 11, 2022 at 4:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.