Re: How to build a btree index with integer values on jsonb data?
On Thu, 6 Dec 2018 at 19:27, Laurenz Albe wrote: > > Replace > > COLLATE pg_catalog."default" > > with > > ::integer which results in syntax error at or near "::" LINE 2: ...'::text) -> 'pub_info'::text) ->> '@pubyear'::text)::integer moving the ::integer into the bracket also: syntax error at end of input LINE 2: ...'::text) -> 'pub_info'::text) ->> '@pubyear'::text::integer) ^ I have tried this before. Thanks for your try. Regards Johann
Re: why would postgres be throttling a streaming replication slot's sending?
On 12/12/18 3:45 μ.μ., Chris Withers wrote:
On 11/12/2018 14:48, Achilleas Mantzios wrote:
On 11/12/18 4:00 μ.μ., Chris Withers wrote:
I'm looking after a multi-tenant PG 9.4 cluster, and we've started getting
alerts for the number of WALs on the server.
It'd be great to understand what's generating all that WAL and what's likely to
be causing any problems.\
Regarding you wals in pg_wal, a good threshold could be anything more than a e.g. 10% increase from wal_keep_segments with a trend to go up. If this number goes up chances are something bad is
happening.
wal_keep_segments is 0 here, so I went hunting in pg_replication_slots and
found that it's the barman slot that's behind on the WAL.
Then your number WALs depends on the traffic generated after the previous checkpoint, the status of archiving and IMO the status of replication slots. The doc :
https://www.postgresql.org/docs/11/runtime-config-replication.html doesn't specifically imply this last one, but it is documented here : https://www.postgresql.org/docs/10/wal-configuration.html , and
it has happened to me. If a replication client with replication_slot dies, WALs accumulate till it wakes up again. (case in pgsql 10)
strace'ing on the barman receiving side shows it waiting on the socket, so no
problem on that side.
on the sending side, it's a little confusing, since the server is basically idle, no cpu or disk activity, and yet strace'ing the sending process attached to the other end of the socket shows time
being spent waiting on a poll which, while it includes the socket being sent to, also includes a bunch of pipes. I've attached a chunk of the output below in the hope that someone on this list could
offer an explanation as to what might cause the WAL to be trickling over this port rather than catching up as fast as it can?
pg_stat_replication is what you should use for monitoring. IIRC you wont see
some cols in the row of the barman.
Also you could query pg_replication_slots as :
select pg_wal_lsn_diff(pg_current_wal_lsn(),confirmed_flush_lsn),pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn) from pg_replication_slots where active_pid=pg_stat_replication>;
pg_wal_lsn_diff | pg_wal_lsn_diff
-+-
312 | 1917736
(1 row)
In the first column you should see diff values (in bytes) close to zero. In the second you should see the length (window) of your replication slot in bytes, which means that this converted to 16MB
should indicate an excess of the number of wals due to the replication slot in addition to the other factors (archiving, checkpoints). In my system it never reaches the size of a single wal.
cheers,
Chris
strace output for the streaming_barman slot servicing the barman replication
slot:
IMO no need to go deep to gdb and stack traces unless you are sure there is a
problem further down.
--- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=3577, si_uid=26} ---
write(4, "\0", 1) = 1
rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call)
read(3, "\0", 16) = 1
read(6, 0x7fffdcd7, 1) = -1 EAGAIN (Resource temporarily
unavailable)
recvfrom(10, 0x7fffdcaf, 1, 0, NULL, NULL) = -1 EAGAIN (Resource
temporarily unavailable)
read(5,
"\3\1\0\0\7\326\355\2\343\0\0\0@\n\0\0\0\17\1\230\313\4\0\0)\347\372l\0\0\0\0"...,
4272) = 4272
sendto(10, "d\0\0\20\315w\0\0\4\313\230\1\17P\0\0\4\313\230\1
\0\0\2\37\321\343\345\306\20\3\1"..., 4302, 0, NULL, 0) = 4302
read(3, 0x7fffdc90, 16) = -1 EAGAIN (Resource temporarily
unavailable)
poll([{fd=10, events=POLLIN}, {fd=3, events=POLLIN}, {fd=6, events=POLLIN}], 3,
26076) = ? ERESTART_RESTARTBLOCK (Interrupted by signal)
--- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=3577, si_uid=26} ---
write(4, "\0", 1) = 1
rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call)
read(3, "\0", 16) = 1
read(6, 0x7fffdcd7, 1) = -1 EAGAIN (Resource temporarily
unavailable)
recvfrom(10, 0x7fffdcaf, 1, 0, NULL, NULL) = -1 EAGAIN (Resource
temporarily unavailable)
read(5, "~\320\5\0\3\0\0\0\0
\1\230\313\4\0\0\3\0\0\0\0\0\0\0\323\316\314\0\0\0\0\0"..., 8192) = 8192
sendto(10, "d\0\0 \35w\0\0\4\313\230\1
\0\0\0\4\313\230\1@\0\0\2\37\321\343\361\376\226~\320"..., 8222, 0, NULL, 0) = 8222
read(3, 0x7fffdc90, 16) = -1 EAGAIN (Resource temporarily
unavailable)
poll([{fd=10, events=POLLIN}, {fd=3, events=POLLIN}, {fd=6, events=POLLIN}], 3,
25275) = ? ERESTART_RESTARTBLOCK (Interrupted by signal)
--- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=3577, si_uid=26} ---
Interesting numbers:
- process 3577 is the wal writer process
- fd 10 is the socket, 3 and 6 are pipes
- time is visibly spent sitting on that poll
- the network is only seeing 2Mbit/sec, which is nothing
cheers,
Chris
--
Achilleas Mantzios
IT DEV Lead
IT
Re: How to build a btree index with integer values on jsonb data?
> "Johann" == Johann Spies writes: Johann> How can I transform the following definition to index pubyear Johann> as integer and not text? Johann> CREATE INDEX pubyear_idx Johann> ON some_table_where_data_field_is_of_type_jsonb USING btree Johann> ((data -> 'REC'::text) -> 'static_data'::text) -> Johann> 'summary'::text) -> 'pub_info'::text) ->> '@pubyear'::text) COLLATE Johann> pg_catalog."default"); Johann> While I can cast the value in a SELECT statement to integer I Johann> have been able to do the same while creating the index. Laurenz' answer was almost correct, just got the position of the parens wrong. When you use an expression in an index, the outermost level of the expression must either be (syntactically) a function call, or it must have parens around its _outermost_ level. You can simplify selecting from nested json using #>> in place of the -> and ->> operators. (x #>> array['foo','bar']) is equivalent to doing ((x -> 'foo') ->> 'bar') So: CREATE INDEX pubyear_idx ON some_table_where_data_field_is_of_type_jsonb USING btree ( ((data #>> array['REC','static_data','summary','pub_info','@pubyear'])::integer) ); Note the ::integer is inside the parens that define the column value within the outermost ( ) which enclose the column _list_. -- Andrew (irc:RhodiumToad)
Re: How to build a btree index with integer values on jsonb data?
Thank you very much. It worked. Regards Johann On Thu, 13 Dec 2018 at 11:03, Andrew Gierth wrote: > > > "Johann" == Johann Spies writes: > > Johann> How can I transform the following definition to index pubyear > Johann> as integer and not text? > > Johann> CREATE INDEX pubyear_idx > Johann> ON some_table_where_data_field_is_of_type_jsonb USING btree > Johann> ((data -> 'REC'::text) -> 'static_data'::text) -> > Johann> 'summary'::text) -> 'pub_info'::text) ->> '@pubyear'::text) COLLATE > Johann> pg_catalog."default"); > > Johann> While I can cast the value in a SELECT statement to integer I > Johann> have been able to do the same while creating the index. > > Laurenz' answer was almost correct, just got the position of the parens > wrong. > > When you use an expression in an index, the outermost level of the > expression must either be (syntactically) a function call, or it must > have parens around its _outermost_ level. > > You can simplify selecting from nested json using #>> in place of the -> > and ->> operators. (x #>> array['foo','bar']) is equivalent to doing > ((x -> 'foo') ->> 'bar') > > So: > > CREATE INDEX pubyear_idx > ON some_table_where_data_field_is_of_type_jsonb USING btree >( > ((data #>> > array['REC','static_data','summary','pub_info','@pubyear'])::integer) >); > > Note the ::integer is inside the parens that define the column value > within the outermost ( ) which enclose the column _list_. > > -- > Andrew (irc:RhodiumToad) -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
pg_top error reporting?
Good day,
I’m running into a problem with pg_top inside a LXC container (unprivileged on
ProxMox).
1) Seems the pgtop “foundry” directory isn’t working?
http://ptop.projects.pgfoundry.org/
Forbidden
You don't have permission to access / on this server.
2) The error is a segmentation fault, looking at strace and ltrace we see the
permission and array check error:
strace:
===
open("5401/io", O_RDONLY) = 4
read(4, 0x7ffd4c67c360, 4095) = -1 EACCES (Permission denied)
close(4)= 0
--- SIGSEGV {si_signo=SIGSEGV, si_code=SEGV_MAPERR, si_addr=0x2} ---
+++ killed by SIGSEGV +++
Segmentation fault
ltrace:
===
[pid 25433] open("5401/io", 0, 07332762347)
= 4
[pid 25433] read(4
error: maximum array length seems negative
, "5401/io", 4095)
= -1
[pid 25433] close(4)
= 0
[pid 25433] strchr("5401/io", ':')
= nil
[pid 25433]
strchr("\002\003\004\005\006\a\b\t\n\v\f\r\016\017\020\021\022\023\024\025\026\027\030\031\032\033\034\035\036\037
!"..., '\n'
[pid 25433] --- SIGSEGV (Segmentation fault) ---
[pid 25433] +++ killed by SIGSEGV +++
signature.asc
Description: Message signed with OpenPGP
Re: pg_top error reporting?
On 12/13/18 4:23 AM, hvjunk wrote: Good day, I’m running into a problem with pg_top inside a LXC container (unprivileged on ProxMox). 1) Seems the pgtop “foundry” directory isn’t working? http://ptop.projects.pgfoundry.org/ Might want to try: https://github.com/markwkm/pg_top Forbidden You don't have permission to access / on this server. -- Adrian Klaver adrian.kla...@aklaver.com
shared_buffers on Big RAM systems
Hi, https://www.postgresql.org/docs/9.6/runtime-config-resource.html The docs say, "If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25%". But that's pretty archaic in 2018. What if the dedicated database server has 128GB RAM? Thanks. -- Angular momentum makes the world go 'round.
why use phpPgAdmin (was: RE: Importing tab delimited text file using phpPgAdmin 5.1 GUI)
> From: Tony Shelver > Just a side comment: Why use phpPgAdmin when pgAdmin 4.6 is current, free and > readily available? > It also has a graphical table-from-file loader as well. I can’t speak for the original poster, but there are multiple reasons that might be: * You came from the mysql world and are used to phpMysqlAdmin. * You tried it long ago and it worked great, so you stuck with it. * You can get phpPgAdmin to work. * You find it hard to make pgAdmin work for you. There may be other reasons, but all of the above are true for me. The most vexing for me is the last one because I'd like it to work well. I just downloaded the latest and am trying to run on Win7. I can only guess why it made me reboot (some MS library), but I did. Then when starting it, it popped up a tab in by browser and that looked promising … until I tried to tell it my server, but it won’t connect (while phpPgAdmin is running just fine in another tab). After enough futzing around with it, I managed to make it connect. After playing with pgAdmin for 5-10 minutes, I’ll say it’s OK but it’s not as easy to use as phpPgAdmin. In fact, telling it to look at new “objects” (tables/views/functions) seems to require multiple clicks and is hard to get what I want on the first try. Then there's the issue that when I stop the server and restart, I can't use the bookmark I made to get to it because it's decided to change ports. What the heck? Anyway, I’m not trying to bash pgAdmin. I’m sure that if I read the docs and used it more, then I’d find it useful. However, my first impression is that it’s harder to use and why switch when I have a perfectly working tool already here. I didn’t have to read any docs to use phpPgAdmin ... a few experimental clicks to see what the controls did and I was able to use it, plus my bookmark for it always works. Now if as someone said that phpPgAdmin won’t work with Pg v11, which I’ll find out in a few months, then I guess I’ll have to revisit this issue, but for the moment, I’m good with phpPgAdmin. Not that expect I it, but if someone from the pgAdmin team wants to contact me for more details, I'll happily explain as maybe it'll help create a better tool. One user’s perspective… Kevin This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.
Re: shared_buffers on Big RAM systems
This topic seems to be always open to discussion. In my opinion, it depends on how big your work dataset is, there's no use in sizing shared_buffers beyond that size. I think, the most reasonable thing is analyzing each case as proposed here: https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/
Re: shared_buffers on Big RAM systems
On Fri, Dec 14, 2018 at 2:17 AM Ron wrote: > https://www.postgresql.org/docs/9.6/runtime-config-resource.html > > The docs say, "If you have a dedicated database server with 1GB or more of > RAM, a reasonable starting value for shared_buffers is 25%". > > But that's pretty archaic in 2018. What if the dedicated database server > has 128GB RAM? I agree, we might as well drop the words "with 1GB of more of RAM". That's the size of the very smallest cloud instances available these days, available for free or up to a few bucks a month, and for physical servers I wonder if you can still get DIMMs that small. -- Thomas Munro http://www.enterprisedb.com
Installing pg_buffercache (was Re: shared_buffers on Big RAM systems)
On 12/13/2018 08:25 PM, Rene Romero Benavides wrote: This topic seems to be always open to discussion. In my opinion, it depends on how big your work dataset is, there's no use in sizing shared_buffers beyond that size. I think, the most reasonable thing is analyzing each case as proposed here: https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/ https://www.postgresql.org/docs/current/pgbuffercache.html Is this an extension or a shared preload library? The documentation doesn't specify. Thanks -- Angular momentum makes the world go 'round.
Re: Installing pg_buffercache (was Re: shared_buffers on Big RAM systems)
Le ven. 14 déc. 2018 à 07:00, Ron a écrit : > On 12/13/2018 08:25 PM, Rene Romero Benavides wrote: > > This topic seems to be always open to discussion. In my opinion, it > > depends on how big your work dataset is, there's no use in sizing > > shared_buffers beyond that size. I think, the most reasonable thing is > > analyzing each case as proposed here: > > > https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/ > > https://www.postgresql.org/docs/current/pgbuffercache.html > > Is this an extension or a shared preload library? The documentation > doesn't > specify. > > It's an extension. -- Guillaume.
Hot Standby Replica Recovery Problem
Hello Experts, We've been having a strange problem with one of our databases. A summary of the setup follows: - We are running postgresql 9.6.9 on Centos 7. - We are using postgresql native streaming replication - There is one master and one hot standby - The master is archiving it's WAL files with this archive command: archive_command = 'test ! -f %p || (test ! -f /var/backup2/dbs1a/pg_xlog/%f.gz && pigz -c %p > /var/backup2/dbs1a/pg_xlog/%f.gz)' # command to use to archive a logfile segment /var/backup2 is a shared gfs2 filesystem. - Recently we had to do some maintenance that involved rebuilding the database servers. At that point dbs1b was the master, so we promoted dbs1a, did the work on dbs1b, and then began establishing a new replica on dbs1b using pg_basebackup - After pg_basebackup completed we set up recovery.conf: restore_command='gzip -dc /var/backup2/dbs1a/pg_xlog/%f.gz > %p' standby_mode='on' primary_conninfo='host=dbs1a user=xx password=x' and then started postgresql. At first it seemed to be starting up as per normal. It began REDO-ing WAL files from the archive. It reached a certain point and then failed with this: [2018-12-14 11:07:39 AEDT] 2050@ LOG: restored log file "00030002C4D70028" from archive [2018-12-14 11:07:39 AEDT] 29240@ LOG: fetching timeline history file for timeline 3 from primary server [2018-12-14 11:07:39 AEDT] 29240@ FATAL: could not receive timeline history file from the primary server: ERROR: could not open file "pg_xlog/0003.history": No such file or directory These messages repeated every few seconds until we stopped the postmaster. I was surprised because we have done this procedure many times on this & other databases, but anyway Dr Google suggested this https://superuser.com/questions/1127360/cant-find-the-timeline-history-file-to-get-the-replication-working. After reading that and other linked articles I decided to create a 'dummy' history file. This time the startup sequence failed like this: [2018-12-14 16:26:46 AEDT] 16575@ LOG: restored log file "00030002C4D70028" from archive [2018-12-14 16:26:47 AEDT] 17669@ LOG: fetching timeline history file for timeline 3 from primary server [2018-12-14 16:26:47 AEDT] 17669@ LOG: started streaming WAL from primary at 2C4D7/2800 on timeline 3 [2018-12-14 16:26:47 AEDT] 17669@ FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 00030002C4D70028 has already been removed Again I was surprised - why wasn't it getting this file from the archives? It is present and does not seem to be truncated or corrupted (as far as I can tell). Anyway in an attempt to force it to get the file from the archives, I removed the primary_conninfo. This time it just repeatedly fetched the file 00030002C4D70028 again and again, never progressing to the next one. Finally I turned up the debugging output, to see this: [2018-12-14 17:58:25 AEDT] 12365@ DEBUG: executing restore command "gzip -dc /var/backup2/dbs1a/pg_xlog/00030002C4D70028.gz > pg_xlog/RECOVERYXLOG" [2018-12-14 17:58:25 AEDT] 12365@ LOG: restored log file "00030002C4D70028" from archive [2018-12-14 17:58:25 AEDT] 12365@ DEBUG: got WAL segment from archive [2018-12-14 17:58:25 AEDT] 12365@ DEBUG: switched WAL source from archive to stream after failure [2018-12-14 17:58:25 AEDT] 12365@ DEBUG: switched WAL source from stream to archive after failure It seems like something is going wrong with the WAL file replay here, although I cannot see what?? I'm writing this email after the second attempt at replication: the first attempt ended much the same. I assumed I had done something wrong so I re-started from the pg_basebackup. Now I am concerned that something is wrong with the master, or maybe with our WAL archiving process. Any tips would be gratefully received! Regards Mike
