On my Ubuntu 20 server, I installed PostgreSQL 13 using the apt manager. I read some articles on performance tuning of PostgreSQL [1] and thought of increasing the WAL segment size (from default of 16MB). I see the following instruction, however, I don't know where to run this. `initdb -D ./data --wal-segment=1024` I guess the documentation refers to installing from PostgreSQL source code, which I don't intend to do. How do I go about change the WAL segment size? [1] https://software.intel.com/content/dam/develop/external/us/en/documents/Open-Source-Database-Tuning-Guide-on-3rd-Generation-Intel-Xeon-Scalable-Processors.pdf Following Daniel's answer, I did the following steps ``` $ pg_lsclusters $ sudo pg_dropcluster --stop 13 main $ sudo pg_createcluster 13 main -- --wal-segsize=256 $ sudo pg_ctlcluster 13 main start ``` You can verify the size of the WAL segments as ``` # du -hcs /var/lib/postgresql/13/main/pg_wal/* ```

We use Postgres 9.6, with 10+TB, with a multi-node cluster setup, managed by Patroni. The WAL archives and backups are managed by home grown tool `pgrsync`. The `archive_command` was initially set to `cp %p /archives/%f`. There is a background job (`pgrsync`) that pushes the archives to S3 periodically. The volume of WAL archives was higher (avg around 200 WAL files/min, with the peak being 500/min). The `cp` also adds to the Disk IO bandwidth, which is precious for us in a cloud environment. We are looking to optimise this in the application. Also, I noticed that in `pg_xlog` folder that several files were hard link to other WAL files. (This part is not understood fully, how could Postgres internally have one WAL archive being a link to another -- it is unlikely that so many transactions could be repeated exactly after some time). Anyway, as an optimisation exercise, we set the `archive_command` to `ln %p /archives/%f`. This reduces the disk IO, we are just adding one more link to the same file. When we are done copying to S3, the link is removed and the OS manages deleting the actual file, when Postgres also frees it. Looks good on paper. Except one problem: If Postgres writes to the same file (with the same inode) after completing the `archive_command`, then we are in a mess. Please refer postgres: WAL ends before end of online backup where we are seeing random WAL corruption and we don't know if using `ln` caused this. Question: Is it safe to use `ln` (hardlink) instead of `cp` in `archive_command`? WAL files are default 16MB. We have a plan to increase it, but I suspect it will reduce the number of files, but the size and IO will likely remain the same. Not likely to help, isn't it? Backup WAL from a standby node is a good idea - or the home grown consumer for archiving. Or even simply `scp` to another server, to reduce disk write IO on Postgres server. Since anything could be standby or master at any time, managing who will actually push files could be s

PostgreSQL works like this, if I have understood things correctly: - I make an INSERT, DELETE or UPDATE query to a table in my PG database. - PG immediately writes this query and its parameters to the HDD/SSD in the form of a raw (internal) log, probably in binary format. - Assuming that the table is small enough to store in RAM, PG now INSERTs/DELETEs/UPDATEs this "RAM table" to reflect the change, but does not actually change the database files. - At some later point, perhaps in seconds, perhaps in minutes, and perhaps even hours or days later, when it "has time", PG updates the database files with the information from this "WAL" log file, and deletes the relevant line(s)/entries from the WAL log file. The explanation I've been given for this seemingly strange ritual is that it's much faster to write to the WAL than it is to write to the database files directly for each query. But why is this? I assume that PG's WAL log file is more sophisticated than just "adding lines to the end of a text file", since that could result in two queries writing to it at once and thus losing or corrupting data. So it has to be more sophisticated than that. Which means more overhead. At which point I wonder what real performance benefit there is to doing this, since both, at the end of the day, involve writing to a slow HDD/SSD disk. However, I'm not doubting this. I'd just like to know why it's so much faster. It seems to me that the problems with writing to the database files are the same with writing to the WAL. In both cases, you need to make sure that it's done orderly and properly. My only guess is that maybe various constraint checks and whatnot are expensive to look up. Apparently, the concept of a "WAL" is unique to PostgreSQL, but it sounds like just common sense to keep at least some sort of "tabs" on what queries have been executed in order to account for power losses and software crashes. Just blindly writing to the database file each time but not even keep any

Is it possible to increase the WAL segment size in an existing PostgreSQL 11 database? What are the disadvantages of having larger WAL segment sizes? The reason for wanting to increase it from 16 MB is to increase the speed of transferring WAL backups when using WAL-E. 16 MB WAL files were transferring at a speed of about 20 MB/s (using SSDs in striped mirrored pairs) which is really slow for database sizes larger than 1 TB. After increasing the WAL segment size, do we typically want to change the values for `min_wal_size` and `max_wal_size`?

We set our `max_wal_size` to 24 GB recently (from the 1 GB default), did some testing, and then set it to 12 GB followed by a server restart. When I query the size of the WAL on the filesystem (total size of files in `pg_xlog` directory), it still shows as about 20 GB. I have issued a manual checkpoint and restarted the server, but the WAL hasn't shrunk back down to 12 GB. This is a pretty simple implementation -- no replication nor archiving in place and there are no long-running transactions that exist. Am I misunderstanding how this is supposed to work? Does it not delete the old WAL files when you lower the `max_wal_size` value and when all transactions have been been completed and the server restarts? Postgres version: 9.6.15 OS: Linux Ubuntu 16.04.6 LTS Settings: max_wal_size = 12GB min_wal_size = 80MB wal_keep_segments = 0 checkpoint_timeout = 15min checkpoint_completion_target = 0.87 wal_compression = off archive_mode = off wal_level = minimal

As I understand from the docs, a WAL segment is archived when the segment file reaches some size or due to `archive_timeout`. So if the database crashes, all the data from the incomplete, currently active WAL segment would be lost (assuming we don't have any access to that incomplete segment in the Postgres directory). Do I get it right and if so, is there a way to avoid this data loss?

I am confused about the Differences BETWEEN the database replication methods mentioned in a wiki page of postgres, which is best for normal situation? - warm-standby/Continuous archiving/log shipping offers high availability http://www.postgresql.org/docs/current/static/warm-standby.html - hot-standby/binary-replication/Streaming Replication used for read-only querie https://wiki.postgresql.org/wiki/Hot_Standby https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial - PITR

I am trying to use the AWS Database Migration Service to migrate data from an RDS Postgres instance to an Aurora MySQL instance. Database Migration Service requires the wal_level to be set as logical in the source Postgres DB. But when done, Postgres started consuming a lot of disk space to the point where the instance became unusable. How long are the WAL logs retained? Are there other settings that can be enabled so disk space use is limited?

I've been scratching my head to solve the following error, but haven't made much progress: ``` $ barman switch-wal --force --archive --archive-timeout 180 pg The WAL file 0000000100000059000000FF has been closed on server 'pg' Waiting for the WAL file 0000000100000059000000FF from server 'pg' (max: 180 seconds) ERROR: The WAL file 0000000100000059000000FF has not been received in 180 seconds ``` I had earlier configured barman on a throwaway VPS and everything worked fine. But when I tried replicating this on our final EC2 instance, I ran into the issue mentioned above. I have ensured the following (even though I don't remember doing this on the VPS): - SSH from barman => pg is working - SSH from pg => barman is working - All firewall rules on 'pg' have been disabled, temporarily What could be the issue here? Edit: Adding the output of `barman check pg` ``` Server pg: 2017-12-08 05:56:31,841 [14559] barman.server ERROR: Check 'WAL archive' failed for server 'pg' WAL archive: FAILED (please make sure WAL shipping is setup) PostgreSQL: OK is_superuser: OK PostgreSQL streaming: OK wal_level: OK 2017-12-08 05:56:34,174 [14559] barman.server ERROR: Check 'replication slot' failed for server 'pg' replication slot: FAILED (slot 'pgbackup' not active: is 'receive-wal' running?) directories: OK retention policy settings: OK 2017-12-08 05:56:34,175 [14559] barman.server ERROR: Check 'backup maximum age' failed for server 'pg' backup maximum age: FAILED (interval provided: 14 days, latest backup age: No available backups) compression settings: OK failed backups: OK (there are 0 failed backups) 2017-12-08 05:56:34,175 [14559] barman.server ERROR: Check 'minimum redundancy requirements' failed for server 'pg' minimum redundancy requirements: FAILED (have 0 backups, expected at least 3) pg_basebackup: OK pg_basebackup compatible: OK pg_basebackup supports tablespaces mapping: OK pg_receivexlog: OK pg_rec

To make updates faster, I am using: ``` ALTER TABLE imagingresourceplanning.opnav_fact_revenue_costs SET UNLOGGED ; ``` What are the drawbacks of this command? What will happen if system crashes during the update? Is all the data present in the table deleted? Or only the updates which are being done will be lost?