Re: PITR and instance without any activity

[Adrien Nayrat]

Hello,

I bump this thread up, I hope to have more reaction :)

TL;DR:

* We can't perform PITR on a cluster without any activity since 13
* It seems creating restore point doesn't record a timestamp in wal.

Thanks

--
Adrien NAYRAT

Re: Exact same output - pg_stat_statements

[hubert depesz lubaczewski]

On Tue, Jan 03, 2023 at 01:52:17PM +0800, Julien Rouhaud wrote:
> Resetting the data adds some noticeable overhead as newly added entries will
> need to generate a normalize query string and so on.  What most people do is
> taking regular snapshots of pg_stat_statements (and other stats) view and then
> compare the snapshots.  There are a few open source projects doing that
> available.

Why do you assume that the cost of normalization query string will be in
any way measurable? Especially in comparison do extra cost of doin math
on potentially thousands of rows?

Best regards,

depesz

Re: PITR and instance without any activity

[Torsten Förtsch]

On Mon, Jan 9, 2023 at 10:59 AM Adrien Nayrat 
wrote:

>
> * We can't perform PITR on a cluster without any activity since 13
> * It seems creating restore point doesn't record a timestamp in wal.
>

I have a cron job that runs this every 5 minutes:

SELECT txid_current() WHERE
(pg_last_committed_xact()).timestamp+'5min'::INTERVAL < now()

 Combine that with a suitable value for archive_timeout.

How do the Linux distributions create the Linux user/group "postgres"?

[Matthias Apitz]

Hello,

Please note: I'm talking about the user and group "postgres" in the
Linux OS and not in the PostgreSQL server.

We're compiling PostgreSQL from source (actually 14.1) and distribute that
to our customers. They're asked to setup user and group "postgres"
before creating the cluster. As nowadays there are a lot of setup such
things in bigger installations, like LDAP or AD, etc. I'd like to know
how other installations for Linux deal with this?

Thanks

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: How do the Linux distributions create the Linux user/group "postgres"?

[Thomas Guyot]

On 2023-01-09 07:41, Matthias Apitz wrote:

Hello,

Please note: I'm talking about the user and group "postgres" in the
Linux OS and not in the PostgreSQL server.

We're compiling PostgreSQL from source (actually 14.1) and distribute that
to our customers. They're asked to setup user and group "postgres"
before creating the cluster. As nowadays there are a lot of setup such
things in bigger installations, like LDAP or AD, etc. I'd like to know
how other installations for Linux deal with this?


Hi Matthias,

Users are generally created by the pre-install or post-install scripts 
of the package. The specific ways in which they are created may depend 
on the packaging tool being used and packager that built the 
distribution package.


Regards,

--
Thomas

Re: How do the Linux distributions create the Linux user/group "postgres"?

[Joe Conway]

On 1/9/23 07:41, Matthias Apitz wrote:

Please note: I'm talking about the user and group "postgres" in the
Linux OS and not in the PostgreSQL server.

We're compiling PostgreSQL from source (actually 14.1) and distribute that
to our customers. They're asked to setup user and group "postgres"
before creating the cluster. As nowadays there are a lot of setup such
things in bigger installations, like LDAP or AD, etc. I'd like to know
how other installations for Linux deal with this?


See for example the PGDG RPM spec file:

https://git.postgresql.org/gitweb/?p=pgrpms.git;a=blob;f=rpm/redhat/main/non-common/postgresql-15/main/postgresql-15.spec;h=60cd42147a7563ba76c401643d0a7c79b59d2520;hb=HEAD

8<---
%pre server
groupadd -g 26 -o -r postgres >/dev/null 2>&1 || :
useradd -M -g postgres -o -r -d /var/lib/pgsql -s /bin/bash \
-c "PostgreSQL Server" -u 26 postgres >/dev/null 2>&1 || :
8<---

HTH,

--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Re: How do the Linux distributions create the Linux user/group "postgres"?

[Erik Wienhold]

> On 09/01/2023 13:41 CET Matthias Apitz  wrote:
>
> Hello,
>
> Please note: I'm talking about the user and group "postgres" in the
> Linux OS and not in the PostgreSQL server.
>
> We're compiling PostgreSQL from source (actually 14.1) and distribute that
> to our customers. They're asked to setup user and group "postgres"
> before creating the cluster. As nowadays there are a lot of setup such
> things in bigger installations, like LDAP or AD, etc. I'd like to know
> how other installations for Linux deal with this?

Arch Linux uses sysusers.d[1] to create user postgres[2][3].

[1] https://man.archlinux.org/man/sysusers.d.5
[2] 
https://github.com/archlinux/svntogit-packages/blob/packages/postgresql/trunk/PKGBUILD#L204
[3] 
https://github.com/archlinux/svntogit-packages/blob/packages/postgresql/trunk/postgresql.sysusers

--
Erik

Re: How do the Linux distributions create the Linux user/group "postgres"?

[Matthias Apitz]

El día Montag, Januar 09, 2023 a las 08:15:33 -0500, Joe Conway escribió:

> On 1/9/23 07:41, Matthias Apitz wrote:
> > Please note: I'm talking about the user and group "postgres" in the
> > Linux OS and not in the PostgreSQL server.
> > 
> > We're compiling PostgreSQL from source (actually 14.1) and distribute that
> > to our customers. They're asked to setup user and group "postgres"
> > before creating the cluster. As nowadays there are a lot of setup such
> > things in bigger installations, like LDAP or AD, etc. I'd like to know
> > how other installations for Linux deal with this?
> 
> See for example the PGDG RPM spec file:
> 
> https://git.postgresql.org/gitweb/?p=pgrpms.git;a=blob;f=rpm/redhat/main/non-common/postgresql-15/main/postgresql-15.spec;h=60cd42147a7563ba76c401643d0a7c79b59d2520;hb=HEAD
> 
> 8<---
> %pre server
> groupadd -g 26 -o -r postgres >/dev/null 2>&1 || :
> useradd -M -g postgres -o -r -d /var/lib/pgsql -s /bin/bash \
>   -c "PostgreSQL Server" -u 26 postgres >/dev/null 2>&1 || :
> 8<---

>From the useradd(8) man page:

CAVEATS
   You may not add a user to a NIS or LDAP group. This must be performed on 
the
   corresponding server.

   Similarly, if the username already exists in an external user database 
such as
   NIS or LDAP, useradd will deny the user account creation request.


-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: How do the Linux distributions create the Linux user/group "postgres"?

[Ron]

On 1/9/23 07:15, Joe Conway wrote:

On 1/9/23 07:41, Matthias Apitz wrote:

Please note: I'm talking about the user and group "postgres" in the
Linux OS and not in the PostgreSQL server.

We're compiling PostgreSQL from source (actually 14.1) and distribute that
to our customers. They're asked to setup user and group "postgres"
before creating the cluster. As nowadays there are a lot of setup such
things in bigger installations, like LDAP or AD, etc. I'd like to know
how other installations for Linux deal with this?


See for example the PGDG RPM spec file:

https://git.postgresql.org/gitweb/?p=pgrpms.git;a=blob;f=rpm/redhat/main/non-common/postgresql-15/main/postgresql-15.spec;h=60cd42147a7563ba76c401643d0a7c79b59d2520;hb=HEAD 



8<---
%pre server
groupadd -g 26 -o -r postgres >/dev/null 2>&1 || :
useradd -M -g postgres -o -r -d /var/lib/pgsql -s /bin/bash \
-c "PostgreSQL Server" -u 26 postgres >/dev/null 2>&1 || :
8<---


What if (for example in an already-installed package) uid and gid 26 already 
exist?


--
Born in Arizona, moved to Babylonia.

Re: How do the Linux distributions create the Linux user/group "postgres"?

[Joe Conway]

On 1/9/23 09:11, Ron wrote:

On 1/9/23 07:15, Joe Conway wrote:

On 1/9/23 07:41, Matthias Apitz wrote:

Please note: I'm talking about the user and group "postgres" in the
Linux OS and not in the PostgreSQL server.

We're compiling PostgreSQL from source (actually 14.1) and distribute that
to our customers. They're asked to setup user and group "postgres"
before creating the cluster. As nowadays there are a lot of setup such
things in bigger installations, like LDAP or AD, etc. I'd like to know
how other installations for Linux deal with this?


See for example the PGDG RPM spec file:

https://git.postgresql.org/gitweb/?p=pgrpms.git;a=blob;f=rpm/redhat/main/non-common/postgresql-15/main/postgresql-15.spec;h=60cd42147a7563ba76c401643d0a7c79b59d2520;hb=HEAD 



8<---
%pre server
groupadd -g 26 -o -r postgres >/dev/null 2>&1 || :
useradd -M -g postgres -o -r -d /var/lib/pgsql -s /bin/bash \
-c "PostgreSQL Server" -u 26 postgres >/dev/null 2>&1 || :
8<---


What if (for example in an already-installed package) uid and gid 26 already
exist?


The "|| :" at the end of those lines is essentially "or true" -- i.e. if 
the first command fails *due to preexisting uid/gid, the line still 
evaluates as successful. Whether that is a good thing or not is in the 
eye of the beholder I guess.


I have thought about, and played with a bit, the idea of making the 
uid/gid in the community spec file a variable so that it could be passed 
in at rpmbuild time. But that quickly bloomed into a host of related 
issues -- e.g. the default "/var/lib/pgsql" for the home dir, the 
default "postgres" for the user/group names, and/or what to do if the 
username does not exist but the directory does, etc -- I ran out of 
energy to chase it all down.


--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

pg_multixact_member file limits

[Martin Ritchie]

Are there any limits on the number of records in
the postgresql/12/main/pg_multixact/members directory? We have a database
that has grown to tens of thousands of files in this directory during an
autovacuum after a large data purge. It shrank after the autovacuum
completed.

Martin Ritchie
*Geotab*
Senior DBA
Direct +1 (519) 741-7660
Toll-free +1 (877) 436-8221
Visit www.geotab.com
Twitter  | Facebook
 | YouTube
 | LinkedIn

Re: Segmentation fault on RelationGetDescr in my first extension

[Kyotaro Horiguchi]

At Thu, 29 Dec 2022 13:52:18 +0300, Дмитрий Цветков  
wrote in 
> I'm trying to write my first extension and open a table in it.
> I use check_password_hook and my function executes at the moment of
> changing user password.
> 
> But if I try to open a table inside this function, I get Segmentation fault
> on the line with   "RelationGetDescr".
> What am I doing wrong?

table_open() should error out when it is going to return an invalid
Relation*, and RelationGetDescr(rel) is "rel->rd_att". So the
RelationGetDescr() cannot get a segv even if the table does not exist.

# I modified contrib/passwordcheck that way and saw RelationGetDescr()
# doesn't get segv in the shown context.

I guess the segv comes from another place, or something else has
broken memory until there. but anyway more information is needed for
people to diagnose your situation more closely.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center