[GENERAL] When files in pg_multixact/{members,offsets} get recycled?
Hello, Running latest 9.3.9. Database was never pg_upgraded. I list pg_multixact/offsets and pg_multixact/members and I see many files with dates more than one year old. Is that ok? # ls -1 members/[0-9A-F]* | wc -l 894 # ls -la members | head total 127064 drwx-- 2 pgsql pgsql 896 Mar 31 2014 . drwx-- 4 pgsql pgsql 4 Dec 11 2013 .. -rw--- 1 pgsql pgsql 262144 Dec 11 2013 -rw--- 1 pgsql pgsql 262144 Dec 11 2013 0001 -rw--- 1 pgsql pgsql 262144 Dec 11 2013 0002 -rw--- 1 pgsql pgsql 262144 Dec 11 2013 0003 -rw--- 1 pgsql pgsql 262144 Dec 11 2013 0004 -rw--- 1 pgsql pgsql 262144 Dec 11 2013 0005 -rw--- 1 pgsql pgsql 262144 Dec 11 2013 0006 # ls -la members | tail -rw--- 1 pgsql pgsql 262144 Mar 30 2014 0374 -rw--- 1 pgsql pgsql 262144 Mar 30 2014 0375 -rw--- 1 pgsql pgsql 262144 Mar 30 2014 0376 -rw--- 1 pgsql pgsql 262144 Mar 30 2014 0377 -rw--- 1 pgsql pgsql 262144 Mar 30 2014 0378 -rw--- 1 pgsql pgsql 262144 Mar 30 2014 0379 -rw--- 1 pgsql pgsql 262144 Mar 31 2014 037A -rw--- 1 pgsql pgsql 262144 Mar 31 2014 037B -rw--- 1 pgsql pgsql 262144 Mar 31 2014 037C -rw--- 1 pgsql pgsql 253952 Jun 24 01:58 037D As shown, only file 037D in members was recently changed. The same in offsets: # ls -1 offsets/[0-9A-F]* | wc -l 60 # ls -la offsets | head total 15795 drwx-- 2 pgsql pgsql 62 Mar 30 2014 . drwx-- 4 pgsql pgsql 4 Dec 11 2013 .. -rw--- 1 pgsql pgsql 262144 Dec 11 2013 -rw--- 1 pgsql pgsql 262144 Dec 11 2013 0001 -rw--- 1 pgsql pgsql 262144 Dec 21 2013 0002 -rw--- 1 pgsql pgsql 262144 Jan 12 2014 0003 -rw--- 1 pgsql pgsql 262144 Jan 29 2014 0004 -rw--- 1 pgsql pgsql 262144 Feb 6 2014 0005 -rw--- 1 pgsql pgsql 262144 Feb 9 2014 0006 # ls -la offsets | tail -rw--- 1 pgsql pgsql 262144 Mar 24 2014 0032 -rw--- 1 pgsql pgsql 262144 Mar 24 2014 0033 -rw--- 1 pgsql pgsql 262144 Mar 25 2014 0034 -rw--- 1 pgsql pgsql 262144 Mar 26 2014 0035 -rw--- 1 pgsql pgsql 262144 Mar 27 2014 0036 -rw--- 1 pgsql pgsql 262144 Mar 28 2014 0037 -rw--- 1 pgsql pgsql 262144 Mar 28 2014 0038 -rw--- 1 pgsql pgsql 262144 Mar 29 2014 0039 -rw--- 1 pgsql pgsql 262144 Mar 30 2014 003A -rw--- 1 pgsql pgsql 172032 Jun 24 01:58 003B As shown, only file 003B in members was recently changed. # pg_controldata /usr/local/pgsql/logs/data pg_control version number:937 Catalog version number: 201306121 Database system identifier: 5896471202307701721 Database cluster state: in production pg_control last modified: Thu Jun 25 14:42:42 2015 Latest checkpoint location: 26FF/685373F0 Prior checkpoint location:26FF/680609E0 Latest checkpoint's REDO location:26FF/680658A0 Latest checkpoint's REDO WAL file:000526FF0068 Latest checkpoint's TimeLineID: 5 Latest checkpoint's PrevTimeLineID: 5 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 1/3898164439 Latest checkpoint's NextOID: 23515628 Latest checkpoint's NextMultiXactId: 3908584 Latest checkpoint's NextMultiOffset: 46799749 Latest checkpoint's oldestXID:3698648295 Latest checkpoint's oldestXID's DB: 131438 Latest checkpoint's oldestActiveXID: 3898164439 Latest checkpoint's oldestMultiXid: 1 Latest checkpoint's oldestMulti's DB: 131438 Time of latest checkpoint:Thu Jun 25 14:42:39 2015 Fake LSN counter for unlogged rels: 0/1 Minimum recovery ending location: 0/0 Min recovery ending loc's timeline: 0 Backup start location:0/0 Backup end location: 0/0 End-of-backup record required:no Current wal_level setting:hot_standby Current max_connections setting: 500 Current max_prepared_xacts setting: 0 Current max_locks_per_xact setting: 64 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Maximum size of a TOAST chunk:1996 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value Data page checksum version: 0 -- Piotr Gasidło -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Replacing uuid-ossp with uuid-freebsd
2015-05-25 16:28 GMT+02:00 Adrian Klaver : > On 05/25/2015 07:17 AM, Piotr Gasidło wrote: >> (...) >> I've moved from Linux to FreeBSD. I've used uuid-ossp. Now I need to >> aply patch to make it work under FreeBSD. This is rather dirty hack. >> So I need to replace it once and for all with uuid-freebsd module. (...) > What version of Postgres? > > So did you see the section at the bottom of this page?: > > http://www.postgresql.org/docs/9.4/interactive/uuid-ossp.html Thanks - this helps. Currently I'm running 9.3.4. My plan was to upgrade to latest 9.3.x and then to 9.4. With your hint it's now easier. -- Piotr Gasidło -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Replacing uuid-ossp with uuid-freebsd
Hello, I've moved from Linux to FreeBSD. I've used uuid-ossp. Now I need to aply patch to make it work under FreeBSD. This is rather dirty hack. So I need to replace it once and for all with uuid-freebsd module. But because in my database I use uuid type and uuid_* functions is not easy: test_uuid=# \d test Table "public.test" Column | Type | Modifiers +--+- id | uuid | not null default uuid_generate_v4() test_uuid=# drop extension "uuid-ossp"; ERROR: cannot drop extension uuid-ossp because other objects depend on it DETAIL: default for table test column id depends on function uuid_generate_v4() HINT: Use DROP ... CASCADE to drop the dependent objects too. test_uuid=# create extension "uuid-freebsd"; ERROR: function "uuid_nil" already exists with same argument types test_uuid=# I can do this: test_uuid=# CREATE OR REPLACE FUNCTION uuid_generate_v4() RETURNS uuid AS '/usr/local/lib/postgresql/uuid-freebsd.so', 'uuid_generate_v4' VOLATILE STRICT LANGUAGE C; CREATE FUNCTION But THIS is now dirty hack - extension uuid-ossp is still there (according to \dx) and I use functions from other, unregistered extension. I think, that I need do some low level hacking on pg_catalog. Any hints how to do it cleanly (eq. replace functions, drop uuid-ossp and add uuid-freebsd). -- Piotr Gasidło -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange replication problem - segment restored from archive but still requested from master
2015-05-25 11:30 GMT+02:00 Guillaume Lelarge :
>> I currently have wal_keep_segments set to 0.
>> Setting this to higher value will help? As I understand: master won't
>> delete segment and could stream it to slave on request - so it will
>> help.
>
>
> It definitely helps, but the issue could still happen.
>
What conditions must be met for issue to happen?
Both archive_command on master and restore_commands are set and working.
Also wal_keep_segments is set.
I see no point of failure - only delay in the case of high WAL traffic
on master:
- slave starts with restoring WALs from archive,
- now, it connects to master and notices, that for last master's WAL
it needs previous one ("the issue"),
- slave asks master for previous WAL and gets it - job done, streaming
replication set, exit
- if unable to get it (WAL traffic is high, and after restoring last
WAL from archive and asking master for next one more than
wal_keep_segments were recycled) it returns to looking WALs in
archive.
Do I get it right?
--
Piotr Gasidło
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange replication problem - segment restored from archive but still requested from master
2015-05-22 6:55 GMT+02:00 Fujii Masao : > > This problem happens when WAL record is stored in separate two WAL files and > there is no valid latter WAL file in the standby. In your case, the former > file > is 00044C4D0090 and the latter is 00044C4D0091. > > In this case, the first half of WAL record can be read from the former WAL > file, > but the remaining half not because no valid latter file exists in the standby. > Then the standby tries to retrieve the latter WAL file via replication. > The problem here is that the standby tries to start the replication from the > starting point of WAL record, i.e., that's the location of the former WAL > file. > So the already-read WAL file is requested via replication. > (..) I currently have wal_keep_segments set to 0. Setting this to higher value will help? As I understand: master won't delete segment and could stream it to slave on request - so it will help. Does this setting delays WAL archiving? -- Piotr Gasidło -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange replication problem - segment restored from archive but still requested from master
sn: 4C4D/910119D0, prev 4C4D/9100FD28, bkp: , desc: freeze_page: rel 1663/131438/10079072; blk 13857; cutoff xid 422325322 ntuples 34 rmgr: Heap2 len (rec/tot): 24/ 7920, tx: 0, lsn: 4C4D/91011BA0, prev 4C4D/910119D0, bkp: 1000, desc: freeze_page: rel 1663/131438/10079072; blk 13858; cutoff xid 422325322 ntuples 60 ... I this is not enought, I can attach both files. -- Piotr Gasidło -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange replication problem - segment restored from archive but still requested from master
47]: [9-1] 2015-05-21 21:30:27.957 CEST @ 54547 LOG: consistent recovery state reached at 4C4D/946CE620 May 21 21:30:27 d8 postgres[54547]: [10-1] 2015-05-21 21:30:27.957 CEST @ 54547 LOG: unexpected pageaddr 4C45/CD6D4000 in log segment 00044C4D0094, offset 7159808 May 21 21:30:27 d8 postgres[54546]: [3-1] 2015-05-21 21:30:27.957 CEST @ 54546 LOG: database system is ready to accept read only connections May 21 21:30:27 d8 postgres[54580]: [4-1] 2015-05-21 21:30:27.969 CEST @ 54580 LOG: started streaming WAL from primary at 4C4D/9400 on timeline 4 May 21 21:30:28 d8 postgres[54581]: [4-1] 2015-05-21 21:30:28.039 CEST pgsql@postgres 54581 [local] [unknown] FATAL: role "pgsql" does not exist May 21 21:36:58 d8 postgres[54564]: [3-1] 2015-05-21 21:36:58.080 CEST @ 54564 LOG: restartpoint starting: time -- And everything goes OK. -- Piotr Gasidło -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Strange replication problem - segment restored from archive but still requested from master
res[53575]: [4-1] 2015-05-21 21:28:55.126 CEST @ 53575 LOG: entering standby mode May 21 21:28:55 d8 postgres[53575]: [5-1] 2015-05-21 21:28:55.150 CEST @ 53575 LOG: restored log file "00044C4D0088" from archive May 21 21:28:55 d8 postgres[53575]: [6-1] 2015-05-21 21:28:55.209 CEST @ 53575 LOG: redo starts at 4C4D/88493B50 May 21 21:28:55 d8 postgres[53575]: [7-1] 2015-05-21 21:28:55.314 CEST @ 53575 LOG: restored log file "00044C4D0089" from archive May 21 21:28:55 d8 postgres[53575]: [8-1] 2015-05-21 21:28:55.512 CEST @ 53575 LOG: restored log file "00044C4D008A" from archive May 21 21:28:55 d8 postgres[53575]: [9-1] 2015-05-21 21:28:55.831 CEST @ 53575 LOG: restored log file "00044C4D008B" from archive May 21 21:28:56 d8 postgres[53575]: [10-1] 2015-05-21 21:28:56.508 CEST @ 53575 LOG: restored log file "00044C4D008C" from archive May 21 21:28:56 d8 postgres[53575]: [11-1] 2015-05-21 21:28:56.818 CEST @ 53575 LOG: restored log file "00044C4D008D" from archive May 21 21:28:56 d8 postgres[53575]: [12-1] 2015-05-21 21:28:56.982 CEST @ 53575 LOG: restored log file "00044C4D008E" from archive May 21 21:28:57 d8 postgres[53575]: [13-1] 2015-05-21 21:28:57.415 CEST @ 53575 LOG: restored log file "00044C4D008F" from archive May 21 21:28:57 d8 postgres[53575]: [14-1] 2015-05-21 21:28:57.790 CEST @ 53575 LOG: restored log file "00044C4D0090" from archive May 21 21:28:58 d8 postgres[53575]: [15-1] 2015-05-21 21:28:58.328 CEST @ 53575 LOG: consistent recovery state reached at 4C4D/90FFF9C8 May 21 21:28:58 d8 postgres[53574]: [3-1] 2015-05-21 21:28:58.328 CEST @ 53574 LOG: database system is ready to accept read only connections May 21 21:28:58 d8 postgres[53575]: [16-1] 2015-05-21 21:28:58.349 CEST @ 53575 LOG: restored log file "00044C4D0091" from archive May 21 21:28:59 d8 postgres[53575]: [17-1] 2015-05-21 21:28:59.962 CEST @ 53575 LOG: restored log file "00044C4D0092" from archive May 21 21:29:00 d8 postgres[53575]: [18-1] 2015-05-21 21:29:00.037 CEST @ 53575 LOG: unexpected pageaddr 4C45/DC00 in log segment 00044C4D0093, offset 0 May 21 21:29:00 d8 postgres[53640]: [4-1] 2015-05-21 21:29:00.047 CEST @ 53640 LOG: started streaming WAL from primary at 4C4D/9200 on timeline 4 What was wrong? Postgresql 9.3.6 on slave and 9.3.4 on master. Unable to retest if master was also 9.3.6. -- Piotr Gasidło -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SELECT 'NOW()' - OK, SELECT 'CLOCK_TIMESTAMP()' - ERROR
2014-08-22 9:05 GMT+02:00 David G Johnston : > > Do you have a use-case you'd like to share or is this curiosity after > accidentally finding out that 'now'::timestamp actually works? > I've found it by accident and I had to ask if it is bug or right way. > -- Piotr Gasidło
[GENERAL] SELECT 'NOW()' - OK, SELECT 'CLOCK_TIMESTAMP()' - ERROR
Hello, I found strange PostgreSQL 9.3 behavior: > select now()::timestamp, 'now()'::timestamp; now | timestamp + 2014-08-22 08:34:00.883268 | 2014-08-22 08:34:00.883268 Second column is now() in single apostrophes. Now, I tried similar function, clock_timestamp() and get: > select clock_timestamp()::timestamp, 'clock_timestamp()'::timestamp; ERROR: invalid input syntax for type timestamp: "clock_timestamp()" LINE 1: select clock_timestamp()::timestamp, 'clock_timestamp()'::ti... ^ Why is NOW() so special? Where is it documented? And why not working with other timestamp returning internal functions? > select version(); version -- PostgreSQL 9.3.4 on amd64-portbld-freebsd10.0, compiled by FreeBSD clang version 3.3 (tags/RELEASE_33/final 183502) 20130610, 64-bit (1 wiersz) -- Piotr Gasidło
Re: [GENERAL] pg_basebackup from new master's slave then recovery from new master.
2013/8/17 Piotr Gasidło : > All on 9.3beta2. Current setup: > (...) Ehh, typo. > 2. I edit recovery.conf on server2 to NOT point to server2 (NEW > MASTER) but server3 (OLD MASTER) and restart server3 Should be: 2. I edit recovery.conf on server3 to NOT point to server2 (NEW > MASTER) but server1 (OLD MASTER) and restart server3 -- Piotr Gasidło -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_basebackup from new master's slave then recovery from new master.
All on 9.3beta2. Current setup: server1 (MASTER) -> server2 (SLAVE) -> server3 (SLAVE) server2 is hot_standby and gets WALs from server1 server3 is hot_standby and gets WALs from server2 In every recovery.conf I have: recovery_target_timeline='latest' Now i do switchover by touching recovery.conf's trigger_file on server2. server1 (OLD MASTER) server2 (NEW MASTER) -> server3 (SLAVE) Then, I take down server1 (OLD MASTER), do fresh pg_basebackup data from server3 (SLAVE) to server1 (OLD MASTER). Now: 1. I edit recovery.conf on server1 to NOT point to server3 (SLAVE) but server2 (NEW MASTER) and start server1 2. I edit recovery.conf on server2 to NOT point to server2 (NEW MASTER) but server3 (OLD MASTER) and restart server3 I get this replication setup: server2 (NEW MASTER) -> server2 (OLD MASTER, SLAVE) -> server3 (SLAVE) Are these (1,2) operations safe? I did it on test environment but I need to be sure if I won't loose any data doing such things. -- Piotr Gasidło -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Raid Controller Write Cache setting for WAL and Data
Hello, Is it safe, to have RAID controller (not BBC) write cache _enabled_ for disks where data are stored and write cache _disabled_ for disks where WAL segments are stored? I _can_ afford of loosing some data in case of power failure. But I'm afraid of having database in unrecoverable state after crash. fsync = on I have 4 disks, 2 for WAL (RAID1) and 2 for data (RAID1). WAL and data are on XFS partitions (nobarrier, noatime). -- Piotr Gasidło -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Difference between EXPLAIN ANALYZE SELECT ... total runtime and SELECT ... runtime
W dniu 23 lipca 2010 10:51 użytkownik Piotr Gasidło
napisał:
> 2010/7/23 A. Kretschmer :
>> Maybe cheaply or virtuell hardware?
> (...)
>> There are some issues with functions
>> like gettimoofday(), see here:
> (..)
Just tested it on my workstation. No vserver. The same result.
EXPLAIN ANALYZE SELECT ...
Total runtime: 72.745 ms
Time: 1916,269 ms
SELECT ...
Time: 2121,198 ms
The table structure is:
ocaches
ocaches_2010_06_0 (inherits ocaches)
ocaches_2010_06_1 (inherits ocaches)
ocaches_2010_06_2 (inherits ocaches)
ocaches_2010_06_3 (inherits ocaches)
ocaches_2010_06_4 (inherits ocaches)
ocaches_2013_06_4 (inherits ocaches)
Constraint checks by date on each partition.
\d ocaches
Table "ocaches"
Column |Type | Modifiers
---+-+---
oc_count | integer |
oc_h_id | integer |
oc_date_from | date|
oc_date_to| date|
oc_duration | integer |
...
Many columns mainly integer and integer[].
Index on column oc_h_id on each paritioned table.
query:
SELECT
oc_h_id,oc_duration,SUM(oc_count) FROM ocaches_joined WHERE
oc_date_from >= '2010-07-22'::date AND oc_date_from >=
'2010-07-24'::date AND oc_h_id =
ANY('{"32842","3095","27929","2229","22769","3098","33433","22559","226","2130","226","2130","2229","3095","3098","22559","22769","27929","32842","33433"}'::int[])
GROUP BY oc_h_id, oc_duration;
--
Piotr Gasidło
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Difference between EXPLAIN ANALYZE SELECT ... total runtime and SELECT ... runtime
2010/7/23 A. Kretschmer : > Maybe cheaply or virtuell hardware? Cheap - probably, I known processor, but don't know mainboard. It's dedicated server. Maybe it has silent problems with time keeping. Virtual - also yes. Postgresql run in separate vserver, but I've executed psql also from that vserver, so there should be no differences. But I'll try to test it without vserver. > There are some issues with functions > like gettimoofday(), see here: > > http://archives.postgresql.org/pgsql-general/2007-01/msg01653.php > (and the whole thread) Thanks, i will look at it. -- Piotr Gasidło -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Difference between EXPLAIN ANALYZE SELECT ... total runtime and SELECT ... runtime
W dniu 22 lipca 2010 21:46 użytkownik Piotr Gasidło napisał: > W dniu 22 lipca 2010 21:34 użytkownik Szymon Guz napisał: > (...) Something new. The query is run against table which has been partitioned into a lot of small tables. When I combine data and put it into one table - the execution of EXPLAIN ANALYZE compares with real SELECT timeing. On paritioned: EXPLAIN ANALYZE SELECT ... Total runtime: 14.790 ms Time: 291,637 ms On one table with data from all partitions and same indexes: EXPLAIN ANALYZE SELECT ... Total runtime: 16.418 ms Time: 17,371 ms Can someone give me clue why EXPLAIN ANALYZE don't work correctly with partitions? -- Piotr Gasidło -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Difference between EXPLAIN ANALYZE SELECT ... total runtime and SELECT ... runtime
W dniu 22 lipca 2010 21:34 użytkownik Szymon Guz napisał: > maybe the query waits on a lock or maybe the returned rows are very big. So shouldn't EXPLAIN ANALYZE be also affected by waiting for lock? The row has width = 313, so it's not big. I've reduced it to witdh = 12 (only one column, int4 type), and get EXPLAIN ANALYZE: Total runtime: 14.788 ms And only SELECT returns: Time: 456,528 ms Or maybe "Total runtime" it's not what I thought it is, and I should look at psql \timing result, which form EXPLAIN ANALYZE is nearly the same like for SELECT: Time: 402,675 ms -- Piotr Gasidło -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Difference between EXPLAIN ANALYZE SELECT ... total runtime and SELECT ... runtime
Hello, I have strange problem. I test/optimize my queries with EXPLAIN ANALYZE. I get for example: Total runtime: 40.794 ms But when I run query without EXPLAIN ANALYZE i get, for example: Time: 539.252 ms Query returns 33 rows. Why? I do checks with psql connected using socket to postgresql server. No SSL. Using PostgreSQL 8.4.4. -- Piotr Gasidło -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
