[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 adrian.kla...@aklaver.com: 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
Re: [GENERAL] Strange replication problem - segment restored from archive but still requested from master
2015-05-25 11:30 GMT+02:00 Guillaume Lelarge guilla...@lelarge.info: 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
[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
, 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
: 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
Re: [GENERAL] Strange replication problem - segment restored from archive but still requested from master
2015-05-22 6:55 GMT+02:00 Fujii Masao masao.fu...@gmail.com: 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
[GENERAL] Strange replication problem - segment restored from archive but still requested from master
: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 david.g.johns...@gmail.com: 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
[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
Re: [GENERAL] pg_basebackup from new master's slave then recovery from new master.
2013/8/17 Piotr Gasidło qua...@barbara.eu.org: 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] 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
2010/7/23 A. Kretschmer andreas.kretsch...@schollglas.com: 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 23 lipca 2010 10:51 użytkownik Piotr Gasidło
qua...@barbara.eu.org napisał:
2010/7/23 A. Kretschmer andreas.kretsch...@schollglas.com:
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
[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
Re: [GENERAL] Difference between EXPLAIN ANALYZE SELECT ... total runtime and SELECT ... runtime
W dniu 22 lipca 2010 21:34 użytkownik Szymon Guz mabew...@gmail.com 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
Re: [GENERAL] Difference between EXPLAIN ANALYZE SELECT ... total runtime and SELECT ... runtime
W dniu 22 lipca 2010 21:46 użytkownik Piotr Gasidło qua...@barbara.eu.org napisał: W dniu 22 lipca 2010 21:34 użytkownik Szymon Guz mabew...@gmail.com 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
