Re: postgres large database backup

[hvjunk]

> On 01 Dec 2022, at 01:19, Hannes Erven  wrote:
> 
> You could also use a filesystem that can do atomic snapshots - like ZFS.
> 
> However, I'm wondering why none of the previous respondents mentioned it?

the reason: 'cause most people are "stuck" on public clouds that does not 
provide that as a "solution" in their cookie cutter (wrongly called "managed") 
solutions...

> Sure, ZFS may have its own performance implications... but my experience is 
> that it makes "safety snapshots" and "on-demand throw-away testing 
> environments" very convenient to use.

Using that myself, but also as a VM in a ProxMox environmenet, I've found (and 
restored) with ProxMox Backup Server goot single snapshot backup solution.

My cases doesn't need the PITR yet

pg_top error reporting?

[hvjunk]

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: Slot issues

[hvjunk]

> On 14 Oct 2018, at 23:10 , Andres Freund  wrote:
> 
> If you created the new basebackup using rsync, and didn't exclude
> pg_replication_slot, it'll have copied the slots from the primary.  And
> thus needs a high enough max_replication_slots to work with them.

Thanks for pointing this out, after I’ve cleared the pg_replslot/ on the 
cascaded standby, it just “works” ;)


signature.asc
Description: Message signed with OpenPGP

Re: building a server

[hvjunk]

> On 03 Des. 2017, at 23:39 , Rob Sargent  wrote:
> On 12/03/2017 02:15 PM, John R Pierce wrote:
>> On 12/3/2017 12:47 PM, Gmail wrote:
>>> So I revisited https://momjian.us/main/writings/pgsql/hw_selection.pdf 
>>> 
>>> Near the end (page 24) I spotted: 
>>> Heavy use of server-side functions might generate significant CPU load
>>> Just want to confirm that this referring to workload on the 
>>> mainly-single-threaded server not an advocacy for more cores.
>>> 
>> well, if you have significant concurrency with high CPU usage, you want both 
>> more AND faster cores
>> 
>> john r pierce, recycling bits in santa cruz
>> 
> Granted!  I suppose I'm looking for confirmation/correction on believing that 
> for the server to make use of multiple cores is more dependent on the nature 
> of the queries handled.  Concurrency should not be a huge problem for this 
> project, though I've put pieces in place to mitigate that issue.

I would not say that much on the “nature of the queries”, but more on the 
“number of concurrent sessions doing queries”. Granted that with the later 
postgresql versions, given you’ve set the needed to enable parallel queries and 
you have “big-enough-tables” some selects does make use of multiple cores.

As they always say: YMMV