Re: [HACKERS] HELP!!! The WAL Archive is taking up all space
Hi all, Thank you for all of your responses. Meanwhile, I will repost this at pgsql-gene...@postgresql.org Regards, Fattah On 09/12/2015, David G. Johnston <david.g.johns...@gmail.com> wrote: > On Tue, Dec 8, 2015 at 4:43 PM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Tue, Dec 8, 2015 at 3:33 AM, FattahRozzaq <ssoor...@gmail.com> wrote: >> >>> Hi all, >>> >>> Please help... >>> >>> I have 1 master PostgreSQL and 1 standby PostgreSQL. >>> Both servers has the same OS Linux Debian Wheezy, the same hardware. >>> >>> Both server hardware: >>> CPU: 24 cores >>> RAM: 128GB >>> Disk-1: 800GB SAS (for OS, logs, WAL archive directory) >>> Disk-2: 330GB SSD (for PostgreSQL data directory, except WAL archive >>> and except pg_log) >>> >>> The part of the configuration are as below: >>> checkpoint_segments = 64 >>> checkpoint_completion_target = 0.9 >>> default_statistics_target = 10 >>> maintenance_work_mem = 1GB >>> effective_cache_size = 64GB >>> shared_buffers = 24GB >>> work_mem = 5MB >>> wal_buffers = 8MB >>> wal_keep_segments = 4096 >>> wal_level = hot_standby >>> max_wal_senders = 10 >>> archive_mode = on >>> archive_command = 'cp -i %p /home/postgres/archive/master/%f' >>> >>> >>> The WAL archive is at /home/postgres/archive/master/, right? >>> This directory consume more than 750GB of Disk-1. >>> Each segment in the /home/postgres/archive/master/ is 16MB each >>> There are currently 47443 files in this folder. >>> >>> I want to limit the total size use by WAL archive to around 200-400 GB. >>> >>> Do I set the segment too big? >>> wal_keep_segments = 4096 >>> checkpoint_segments = 64 >>> >>> What value should I set for it? >>> >> >> In which case you need to calculate how long it takes to accumulate that >> much archive data and then perform a base backup roughly that often after >> which point any WAL older that the point at which you began the backup >> can >> be removed. >> >> You cannot just limit how large the WAL archive is since removing any WAL >> file will pretty much make any attempt at restoration fail. >> >> David J. >> >> > While valid I missed that you have a streaming replica on the other end > that should be removing files as they are loaded pending the retention > setting...see Michael's response for better information. > > David J. > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] HELP!!! The WAL Archive is taking up all space
Hi all, Please help... I have 1 master PostgreSQL and 1 standby PostgreSQL. Both servers has the same OS Linux Debian Wheezy, the same hardware. Both server hardware: CPU: 24 cores RAM: 128GB Disk-1: 800GB SAS (for OS, logs, WAL archive directory) Disk-2: 330GB SSD (for PostgreSQL data directory, except WAL archive and except pg_log) The part of the configuration are as below: checkpoint_segments = 64 checkpoint_completion_target = 0.9 default_statistics_target = 10 maintenance_work_mem = 1GB effective_cache_size = 64GB shared_buffers = 24GB work_mem = 5MB wal_buffers = 8MB wal_keep_segments = 4096 wal_level = hot_standby max_wal_senders = 10 archive_mode = on archive_command = 'cp -i %p /home/postgres/archive/master/%f' The WAL archive is at /home/postgres/archive/master/, right? This directory consume more than 750GB of Disk-1. Each segment in the /home/postgres/archive/master/ is 16MB each There are currently 47443 files in this folder. I want to limit the total size use by WAL archive to around 200-400 GB. Do I set the segment too big? wal_keep_segments = 4096 checkpoint_segments = 64 What value should I set for it? Regards, Fattah -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] How to import PostgreSQL 9.2.4 dump to PostgreSQL 9.4.5?
Hi guys, I'm running some test. However, I'm stuck in restoring a PostgreSQL 9.2.4 dump to PostgreSQL 9.4.5 database. I'm doing the backup using pg_dump version PostgreSQL 9.2.4 in text file (SQL dump). I'm trying to restore it to PostgreSQL 9.4.5 usng psql. The restoration error message was as below: psql:room.sql:41695: invalid command \n Query buffer reset (cleared). psql:room.sql:41696: invalid command \n Query buffer reset (cleared). The SQL statement in the file as below: COPY room (id, user_id, partner_id, rm_cr_ts, rm_up_ts, rm_sv_ts, value, last_dlvrd_to_user, last_dlvrd_to_prtnr, last_seen_by_user, last_seen_by_prtnr, num_unseen_by_user, num_unseen_by_prtnr, last_message_id, last_message_ts, legacy_id, legacy_last_message_id, inserted_at, updated_at) FROM stdin; 711675 1391103 2657167 -1429528649798556 1429528649798556 1429528649798556created 0 0 0 0 1 0 197334511429528649798556AUzWjCFTbRHQVpj5SYnD AUzWjCR43gMouVUpyttw2015-09-12 18:56:49 2015-09-12 18:56:49 19 40868 1191438 14267677738225381426767773822538 1426767773822538closed 1426754122000 1426754122002 0 1 \N 0 550a8a4a73656d2e26162015-09-12 14:05:32 2015-09-12 14:05:32 I create the room table as below: CREATE TABLE room ( id integer NOT NULL, user_id character varying(255), partner_id character varying(255), rm_cr_ts bigint, rm_up_ts bigint, rm_sv_ts bigint, value character varying(255), last_dlvrd_to_user bigint, last_dlvrd_to_prtnr bigint, last_seen_by_user bigint, last_seen_by_prtnr bigint, num_unseen_by_user integer, num_unseen_by_prtnr integer, last_message_id integer, last_message_ts bigint, legacy_id text, legacy_last_message_id text, inserted_at timestamp without time zone NOT NULL, updated_at timestamp without time zone NOT NULL ); All the servers are Debian 7.8 (Wheezy) with hardware: - CPU 24 cores - RAM 128GB - SSD 128GB Have anyone try this before? How to do it properly? Thanks in advance, Fattah -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
