Reducing bandwidth usage of database replication

[Sascha Zenglein]

Hi all,

I want to use the postgres-native logical replication to have multiple clients 
receive and send data to a central database.
Real-time is far less important than network usage, and with my current test 
setup it appears both instances communicate frequently if a subscription is 
active, even if nothing is happening.

Is there a good way to reduce data usage, for example by limiting the amount of 
keep-alive messages? One database will likely be idle most of the time.

I estimated the current solution to idle at around 1.4MiB per day. Ideally it 
would use less than 100KiB a day.

I'm also open for other solutions if anything comes to mind!

Thanks for the help!

Sascha Zenglein

Produktentwicklung


[cid:gessler_email_logo_23bb5200-2c8c-4c2d-a63e-71d5bf29d89f.gif]





Gessler GmbH
Gutenbergring 14
63110 Rodgau
Deutschland

Tel.:  +49 6106 8709 693
Fax:  +49 6106 8709 50

E-Mail: zengl...@gessler.de
Web: www.gessler.de 




Geschaeftsfuehrer: Helmut Gessler, Dipl.-Ing. Marcus Gessler
Gerichtsstand: Offenbach/Main, Amtsgericht Offenbach HRB 20857
USt.-IdNr.: DE 113 551 141
Sparkasse Dieburg, IBAN DE94 5085 2651 0057 0025 03, SWIFT HELADEF1DIE


Diese E-Mail enthaelt vertrauliche und/oder rechtlich geschuetzte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtuemlich 
erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail
und/oder der Inhalte dieser Mail ist nicht gestattet.

This e-mail may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail.
Any unauthorised copying, disclosure or distribution of the material of this 
e-mail is strictly forbidden.

Re: Reducing bandwidth usage of database replication

[Ron]

On 11/2/22 09:56, Sascha Zenglein wrote:

Hi all,

I want to use the postgres-native logical replication to have multiple 
clients receive and send data to a central database.
Real-time is far less important than network usage, and with my current 
test setup it appears both instances communicate frequently if a 
subscription is active, even if nothing is happening.


Is there a good way to reduce data usage, for example by limiting the 
amount of keep-alive messages? One database will likely be idle most of 
the time.


I estimated the current solution to idle at around 1.4MiB per day. Ideally 
it would use less than 100KiB a day.


1.4MiB/day is 17 *bytes* per second.  That's not too much.

--
Angular momentum makes the world go 'round.

Re: Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all"

[Bryn Llewellyn]

> david.g.johns...@gmail.com EARLIER wrote:
> 
> The postgres o/s user should be able to login using peer. It is a one-way 
> idea though. Wanting to login using peer says nothing about whether the user 
> getting that capability should be allowed to mess with the running server in 
> the operating system.
> 
> As for the rest, all I see is that you are using an opinionated package 
> manager to install software whose opinions you don't agree with. Maybe there 
> is some buggy behavior with respect to shared o/s db administration among 
> users in a common group... you haven't demonstrated that one way or the other 
> here. I think it is pointless to have the o/s admin and postgres bootstrap 
> user be anything but postgres and this whole thing is counter-productive. But 
> if you are going down to first principles maybe you should install from 
> source and build your own "package" from that.

> david.g.johns...@gmail.com LATER wrote:
> 
> I think the intent of the design is for the custom Debian wrapper scripts to 
> be able to read the configuration files for the named version "11" and 
> configuration "main" to find out where certain things like the socket file 
> are being written to. The argument being the configuration files don't 
> actually contain secret data so reading shouldn't be an issue and can be 
> useful. Obviously the same does not apply to data files. On that basis it 
> would indeed make more sense to grant read to "all" rather than try and add 
> users to "postgres" to make the reading of the configuration files work.
> 
> Also, per the initdb documentation:
> 
> For security reasons the new cluster created by initdb
> will only be accessible by the cluster user by default.  The
> --allow-group-access option allows any user in the same
> group as the cluster owner to read files in the cluster.  This is useful
> for performing backups as a non-privileged user.

A strange mutual misunderstanding has arisen here. I suppose that it must be my 
fault. I have no interest whatsoever in "going down to first principles". And I 
most certainly never said that I want to "have the o/s admin and postgres 
bootstrap user be anything but postgres". On the contrary: I want just that. 
Saying this more abstractly, I want to install PG (admittedly the old version 
11) in a freshly created Ubuntu 20.04 LTS VM. And I want to follow the reigning 
notions of proper practice. As far as possible, I'd like to find that I simply 
get such an outcome without explicit intervention—or at least by accepting all 
the defaults.

Searching the actual PG doc took me here:

Chapter 17. Installation from Source Code
https://www.postgresql.org/docs/15/installation.html

That's the last thing I want to do. So then I read this:

Chapter 16. Installation from Binaries
https://www.postgresql.org/docs/15/install-binaries.html

(It was ranked lower by the doc's native search.) It says nothing of substance. 
But it does say this:

«
visit the download section on the PostgreSQL website at
https://www.postgresql.org/download/ 
and follow the instructions for the specific platform.
»

I did exactly that. And I selected "Linux" and under that "Ubuntu". Notice that 
I did NOT select "Debian", though it was on offer, because that's not what I 
have. If Ubuntu and Debian were effectively the same, then there wouldn't be 
two distinct choices. My choice took me here:

Linux downloads (Ubuntu)
https://www.postgresql.org/download/linux/ubuntu/

It mentions that my (22.04, LTS) is supported. Then I did these simple steps:

sudo -s
apt install postgresql-common
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

apt update
apt upgrade
apt install postgresql-11

I did have to look around a bit to find that recipe. But it completed quickly, 
without error, and without prompting me to make a single choice.

Now that I know what I do (and I confess that I did not know enough at the 
start) I could complete the whole thing in less than 30 minutes. (The time 
would be more or less according to what notes I decided to take along the way 
and what copy-and-paste-ready config file snippets and the like I had to hand.) 
I'm including, in this timing, the necessary post install steps to allow 
connections from other machines and to enable "local", "peer" authorization for 
my "superuser's assistant" that I implement with the cluster-role that I name 
"clstr$mgr".

I did the whole thing from scratch after trashing my provisional attempt. (This 
is easily afforded when you use a VM. Am I the only person who does this: 
practice, make mistakes, learn, trash, and then do it for real?)

I discovered this time around that the config files "arrive" like this:

-rw-r--r-- 1 postgres postgres   143 Nov  1 15:48 
/etc/postgresql/11/main/pg_ctl.conf
-rw-r- 1 postgres postgres  4686 Nov  1 15:48 
/etc/postgresql/11/main/pg_hba.conf
-rw-r- 1 postgres postgres  1636 Nov  1 15:48 
/etc/postgresql/11

Re: Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all"

[Adrian Klaver]

On 11/2/22 15:31, Bryn Llewellyn wrote:
david.g.johns...@gmail.com  EARLIER 
wrote:




A strange mutual misunderstanding has arisen here. I suppose that it 
must be my fault. I have no interest whatsoever in "going down to first 
principles". And I most certainly never said that I want to "have the 
o/s admin and postgres bootstrap user be anything but postgres". On the 
contrary: I want just that. Saying this more abstractly, I want to 
install PG (admittedly the old version 11) in a freshly created Ubuntu 
20.04 LTS VM. And I want to follow the reigning notions of proper 
practice. As far as possible, I'd like to find that I simply get such an 
outcome without explicit intervention—or at least by accepting all the 
defaults.




I did exactly that. And I selected "Linux" and under that "Ubuntu". 
Notice that I did NOT select "Debian", though it was on offer, because 
that's not what I have. If Ubuntu and Debian were effectively the same, 
then there wouldn't be two distinct choices. My choice took me here:


They use the same basic packaging and wrapper mechanism. They are 
separated out because, very simple explanation, Ubuntu is downstream of 
Debian and the version releases are not the same.




In summary, then, I followed a link from the PG doc to a site whose URL 
makes it sound "official", selected my environment, and followed some 
simple steps as specified. That doesn't sound like "opinionated" to me. 
Nor does it sound like stubbornly insisting on doing things my own way.


It is official as these are the community released packages.

The opinionated reference was to what the Debian/Ubuntu packaging does, 
not you.




Adrian gave me this link:

https://ubuntu.com/server/docs/databases-postgresql 



Of course I'd read that right at the outset. The subtext is loud and 
clear. You need to do some things as the "postgres" user and some things 
as "root". That's why I enabled "sudo" for "postgres" (just as the code 
examples on that page imply).


You don't need to do that. Just use sudo as what ever user you log in 
as. For example:


aklaver@arkansas:~$ sudo pg_ctlcluster stop 14/main
[sudo] password for aklaver:

Again very simple explanation, the OS postgres user is just created to 
run the server. It does not even have a home directory.




However, I have no a priori requirement to use the root-needing 
"systemctl" to stop and start my cluster. But I do want to be able to 
use "initdb" so that I can simply trash an extant cluster and start 
again from a well-defined, pristine state. (I want to do this to be 
completely sure that my own scripts make no assumptions about 
pre-existing objects.) This script works perfectly well:


*sudo systemctl stop postgresql

rm -Rf /var/lib/postgresql/11/main

initdb \
   -U postgres --encoding UTF8 --locale=C --lc-collate=C 
--lc-ctype=en_US.UTF-8 \

   -A md5 --pwfile=/etc/ybmt-code/misc/.superuser-passwd-for-initdb \
   -D /var/lib/postgresql/11/main

sudo systemctl start postgresql

psql -f 
*
But it does require that my "postgres" O/S user is enabled for "sudo" — 
which you all insist is a terrible crime, even on my personal laptop 
that nobody else can access. Moreover, I'm using "initdb". This is 
apparently verboten (it isn't exposed via a link on "/usr/bin")—in the 
env produced by the installation procedure that the PG doc lead me to. 
The same is true for "pg_ctl" and "postgres". A bit of Googling took me 
here:


Again, when in Rome:

sudo pg_createcluster 15 test
Creating new PostgreSQL cluster 15/test ...
/usr/lib/postgresql/15/bin/initdb -D /var/lib/postgresql/15/test 
--auth-local peer --auth-host scram-sha-256 --no-instructions
The files belonging to this database system will be owned by user 
"postgres".

This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/15/test ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Los_Angeles
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Ver Cluster Port Status OwnerData directory  Log file
15  test5433 down   postgres /var/lib/postgresql/15/test 
/var/log/postgresql/postgresql-15-test.log


man pg_createcluster

for more options.


So, apparently, Debian and Ubuntu are the same after all, even though 
they have separate install pages from www.postgresql.org/download/linux 
.


The same as Debian and Ubuntu are.

Re: Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all"

[David G. Johnston]

Some repetition of what Adrian just posted ahead...

On Wed, Nov 2, 2022 at 3:31 PM Bryn Llewellyn  wrote:

>
> I did exactly that. And I selected "Linux" and under that "Ubuntu". Notice
> that I did NOT select "Debian", though it was on offer, because that's not
> what I have. If Ubuntu and Debian were effectively the same, then there
> wouldn't be two distinct choices.
>

Some of what I wrote assumed a familiarity with the Linux distribution
ecosystem...Debian originated the opinions about how to install PostgreSQL;
Ubuntu, by virtue of building upon that distribution, inherited those
opinions.  In particular, they are considerably different than what
CentOS/Red Hat thinks.


> I did the whole thing from scratch after trashing my provisional attempt.
> (This is easily afforded when you use a VM. Am I the only person who does
> this: practice, make mistakes, learn, trash, and then do it for real?)
>

I do this all of the time myself - leveraging Ansible for
infrastructure-as-code as well.


> In summary, then, I followed a link from the PG doc to a site whose URL
> makes it sound "official", selected my environment, and followed some
> simple steps as specified. That doesn't sound like "opinionated" to me.
>

Debian/Ubuntu are opinionated, and enforce those opinions via the Apt-based
packaging that their community creates from the PostgreSQL source code.
There are many such communities out there (BSD, Windows, Red Hat, etc...)
and basically no one within core is interested in worrying about how those
different operating systems work at the DBA level.  A generally shared
adherence to POSIX and the facilities provided by the C language make that
practical.


>
>
> https://ubuntu.com/server/docs/databases-postgresql
>
> Of course I'd read that right at the outset. The subtext is loud and
> clear. You need to do some things as the "postgres" user and some things as
> "root". That's why I enabled "sudo" for "postgres" (just as the code
> examples on that page imply).
>

Honestly, a server running PostgreSQL should have, at minimum, three
relevant users.  Root, Postgres, and the user the system admin logs in as.
This third user should sudo to install PostgreSQL, su to initially
configure the system using the cluster owner (see my -hackers email for the
documentation patches this has inspired), then sudo again to create any
additional local users you might want if you aren't going to use the
postgres user name the packaging gives you for everyday use.  The elided
script below should be run as your system admin user, not root nor postgres
(or just run it as root and su for the initdb part).


>
> But it seems that I'm now in a regime where critical PG utilities don't
> work like the PG doc says, and where what you need, "pg_ctlcluster", isn't
> even mentioned in the PG doc. (I do see that it's present in my env and is
> properly wired up from "/usr/bin". (Actually, it's right there on that
> directory as an executable file.)
>
> How can it be that the PG doc itself leads you by the hand to a regime
> where you need to use undocumented features?
>

The documentation tries to make clear that if you use third-party packaging
to install PostgreSQL (which most people should) that the documentation for
the packaging should describe this layer where PostgreSQL and the operating
system intersect.  You even quoted it: "follow the instructions for the
specific platform.", though reading that now I think something along the
lines of:

 "Additionally, while reading the next chapter, Server Setup and Operation,
is recommended if you are using a binary package the setup and operational
environment it creates is likely to be somewhat different than what is
described in this documentation.  Please read the documentation for the
packages you install to learn how it behaves and what additional
platform-specific features it provides."

I haven't publicly (at least not recently...) voiced an opinion on the
quality of the Apt documentation, nor have volunteered to work on it.  But
regardless it is an entirely different department run by volunteers that
package up many different applications, not just PostgreSQL.  That
decentralization and spreading out of responsibilities is simply how this
overall community is structured and your frustrations stem a great deal
from this particular seam.


> Meanwhile, can I appeal to one of you simply to tell me, here, the magic
> spells that I must write so that I can remain as the "postgres" O/S user
> and achieve my "trash an extant cluster and start again from a
> well-defined, pristine state" goal?
>
> p.s. I did attempt (in a VM that I then trashed) to use "pg_ctl" and
> "initdb". It all "worked" in that there were no errors and I got a new
> cluster.
>

I think...

https://manpages.ubuntu.com/manpages/trusty/man8/pg_dropcluster.8.html

pg_dropcluster --stop 11 main
pg_createcluster 11 main

Again, I don't presently have a desire to investigate the usability of the
Apt packaging's documentatio

Re: Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all"

[David G. Johnston]

On Wed, Nov 2, 2022 at 6:22 PM David G. Johnston 
wrote:

> Some repetition of what Adrian just posted ahead...
>
> On Wed, Nov 2, 2022 at 3:31 PM Bryn Llewellyn  wrote:
>
>>
>> How can it be that the PG doc itself leads you by the hand to a regime
>> where you need to use undocumented features?
>>
>
> The documentation tries to make clear that if you use third-party
> packaging to install PostgreSQL (which most people should) that the
> documentation for the packaging should describe this layer where PostgreSQL
> and the operating system intersect.  You even quoted it: "follow the
> instructions for the specific platform.", though reading that now I think
> something along the lines of:
>
>  "Additionally, while reading the next chapter, Server Setup and
> Operation, is recommended if you are using a binary package the setup and
> operational environment it creates is likely to be somewhat different than
> what is described in this documentation.  Please read the documentation for
> the packages you install to learn how it behaves and what additional
> platform-specific features it provides."
>
>
Actually, not sure on the best approach here, since the Server Setup
chapter already says:

https://www.postgresql.org/docs/current/runtime.html

"The directions in this chapter assume that you are working with plain
PostgreSQL without any additional infrastructure, for example a copy that
you built from source according to the directions in the preceding
chapters. If you are working with a pre-packaged or vendor-supplied version
of PostgreSQL, it is likely that the packager has made special provisions
for installing and starting the database server according to your system's
conventions. Consult the package-level documentation for details."

However, that appears below-the-fold after a decent sized table of contents.

Changing anything now feels like an over-reaction to a single incident, but
I sympathize with the general confusion all this causes, and the fact it is
only in the recent past that we've made this first attempt to rectify the
situation by adding these comments.  A second-pass based upon this
encounter seems at least reasonable.  Whether I or others end up deciding
it is worth proposing a patch remains to be seen.

David J.

Re: Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all"

[Rob Sargent]

 "Additionally, while reading the next chapter, Server Setup and 
Operation, is recommended if you are using a binary package the setup 
and operational environment it creates is likely to be somewhat 
different than what is described in this documentation.  Please read 
the documentation for the packages you install to learn how it behaves 
and what additional platform-specific features it provides."



I wonder if "binary package" would confuse some apt/yum/etc users?  
Maybe "package supplied from a distributor"?

unable to install postgreql 13.4

[shashidhar Reddy]

Hello,

I need to install postgresql 13.4 on development server , but getting below
errors, please help to resolve this

sudo apt-get install postgresql-13.4

Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package postgresql-13.4
E: Couldn't find any package by glob 'postgresql-13.4'
E: Couldn't find any package by regex 'postgresql-13.4'

Note: This is on ubuntu and postgresql 11 is already there on the machine.




-- 
Shashidhar

Re: unable to install postgreql 13.4

[Julien Rouhaud]

Hi,

On Thu, Nov 03, 2022 at 12:18:05PM +0530, shashidhar Reddy wrote:
>
> I need to install postgresql 13.4 on development server , but getting below
> errors, please help to resolve this
>
> sudo apt-get install postgresql-13.4
>
> Reading package lists... Done
> Building dependency tree
> Reading state information... Done
> E: Unable to locate package postgresql-13.4
> E: Couldn't find any package by glob 'postgresql-13.4'
> E: Couldn't find any package by regex 'postgresql-13.4'
>
> Note: This is on ubuntu and postgresql 11 is already there on the machine.

You need to specify the major version but not the minor version.  The package
will take care of installing the latest version, and updating it with the rest
of the system, so you should write:

sudo apt-get install postgresql-13

You can refer to the pgdg apt documentation for more details:
https://wiki.postgresql.org/wiki/Apt.