Re: building a singularity image from docker hub postgres image

[Justin Clift]

On 2024-01-30 15:52, Allan Kamau wrote:

I am trying to build a singularity image from postgres docker image.
I am issuing the command below.

$ singularity build /local/data/some/postgres.16.1.sif
docker://postgres/postgres:16.1

INFO:Starting build...
INFO:Fetching OCI image...
FATAL:   While performing build: conveyor failed to get: GET
https://index.docker.io/v2/postgres/postgres/manifests/16.1: 
UNAUTHORIZED:

authentication required; [map[Action:pull Class: Name:postgres/postgres
Type:repository]]

What is the url I should use?


Not personally familiar with Singularity, but this post looks like it
should help:

  
https://www.linuxwave.info/2022/04/running-postgresql-database-using.html


The example command they have there is:

  singularity pull docker://postgres:14.2-alpine3.15

So the format of the url *seems* like it should be:

  docker://postgres:16.1

That just pure guess work though. :)

Regards and best wishes,

Justin Clift

Re: Monitoring logical replication

[Shaheed Haque]

This is great, thank you for posting. I'm currently a subcontinent or two
away from my dev env, but will compare your approach with mine (you are
using some facilities of psql I'm not familiar with). At least you have
confirmed that LSNs are the place to start.

Thanks again, Shaheed


On Tue, 30 Jan 2024, 05:15 Klaus Darilion, 
wrote:

> Hi Saheed!
>
> I monitor our replication this way:
>
> 1. Every 10 seconds i fetch the current LSN and write it into a table,
> next with the current timestamp. Further I fetch confirmend LSNs from
> the replication slots and delete old entries in lsn2data table.
>
> calculate_logical_replication_lag.php:
>
> 
> $path = realpath(dirname(__FILE__) . "/../inc");
> set_include_path($path . PATH_SEPARATOR . get_include_path());
>
> require_once('config.php');
> $config_int['syslogprefix'] = basename(__FILE__);
> require_once('logging.php');
>
> $dbuser="replication_lag_user";
> $dbpass="";
> if (!$dbconn = pg_pconnect('host='.$config_int['dbhost'].'
> dbname='.$config_int['dbname'].' user='.$dbuser.' password='.$dbpass)) {
>  print "Sorry, database connection failed";
>  exit;
> }
>
> $accuracy = 10; // in seconds
>
> //
> // Preparations:
> //
> // CREATE TABLE lsn2data(
> //lsn pg_lsn PRIMARY KEY,
> //seen timestamp NOT NULL DEFAULT NOW()
> // );
> // CREATE ROLE replication_lag_user WITH LOGIN PASSWORD
> 'XXX';
> // GRANT ALL ON TABLE lsn2data TO replication_lag_user;
> //
> // CREATE OR REPLACE FUNCTION get_replication_lag() RETURNS TABLE
> (subscriber name, lag bigint) AS
> // $BODY$
> // DECLARE
> // subscriber name;
> // BEGIN
> // FOR subscriber IN
> // SELECT slot_name FROM pg_replication_slots
> // LOOP
> // RETURN QUERY SELECT slot_name, EXTRACT(EPOCH FROM
> NOW()-seen)::bigint lag from lsn2data,pg_replication_slots WHERE
> slot_name=subscriber AND lsn < confirmed_flush_lsn ORDER BY lsn DESC
> LIMIT 1;
> // END LOOP;
> // RETURN;
> // END
> // $BODY$
> // LANGUAGE plpgsql;
> //
> while (1) {
>  $dbq = pg_query("INSERT INTO lsn2data (lsn) VALUES
> (pg_current_wal_lsn())");
>  if ($dbq === FALSE) {
>  mylog(LOG_ERROR, "SQL query error:
> ".pg_last_error()."\n");
>  exit(1);
>  }
>
>  $dbq = pg_query("DELETE FROM lsn2data WHERE lsn < (".
>"SELECT lsn FROM lsn2data WHERE lsn < (".
>  "SELECT confirmed_flush_lsn FROM
> pg_replication_slots ORDER BY confirmed_flush_lsn ASC LIMIT 1".
>") ORDER BY lsn DESC LIMIT 1".
>  ")"
>  );
>  if ($dbq === FALSE) {
>  mylog(LOG_ERROR, "SQL query error:
> ".pg_last_error()."\n");
>  exit(1);
>  }
>  sleep($accuracy);
> }
>
> 2. I graph the replications lags (converted from LSN to seconds) in my
> check_mk monitoring:
>
> #!/bin/bash
>
> #
> # Managed by Puppet:
> modules/base/files/monitoring/check_logical_replication_lag.sh
> #
> # Check the logical replication lag and export performance data for each
> subscriber
> #
>
> # exit on error
> #set -e
>
> #Make sure this script only runs one at a time
> (
>
>ME=$0
>MEBASE=`basename $0`
>
>mylog () {
>  echo "$MEBASE: $1"
>  logger -t "$MEBASE" "$1"
>}
>
>flock -x -w 1 200
>if [ $? != "0" ]; then
>  #echo "ERROR: $0 is already running ... exit"
>  logger -t "$MEBASE" "ERROR: $0 is already running ... exit"
>  exit 1
>fi
>
># Do stuff
>
> # Variablen fuer Monitoring
> CMK_SPOOLDIR=/var/lib/check_mk_agent/spool
> CMK_NAME=$MEBASE
> CMK_SPOOLFILE=600_`basename ${CMK_NAME}`.txt
> CMK_HEADER="<<>>"
> TMP_FILE="/tmp/logical_replication_lag.csv"
>
> # Schwellwerte
> warn=300
> crit=600
>
> final_output="$CMK_HEADER\nP $CMK_NAME "
>
> # move to a directory where user postgresl may reside (sudo)
> cd /tmp
>
> # Lag auslesen. Waehrend dem initialen aufsynchen eines Subscribers gibt
> es temporaere Subscriptions, mit dem Namen reg_xxx1-pid-sync-pid.
> # Damit diese nicht getrackt werden gibt es die huebsche LIKE Clause.
> rm -f "$TMP_FILE"
> sudo -u postgres psql regdns -c "COPY (SELECT subscriber,lag FROM
> get_replication_lag() WHERE subscriber LIKE '%\_' ORDER BY 2 DESC)
> TO '$TMP_FILE' With CSV" 2>&1> /dev/null
> LC=$(sudo -u postgres psql -t regdns -c "SELECT count(*) FROM
> get_replication_lag();" | tr -d ' ')
>
> if [ $LC == "0" ]; then
>  echo -e "$CMK_HEADER\n0 $CMK_NAME - No Slaves with Replication
> found - maybe we are a slave?" > $CMK_SPOOLDIR/$CMK_SPOOLFILE
>  exit 0;
> fi
>
> grep $(hostname | cut -d '-' -f2) "$TMP_FILE" > /dev/null
> if [ $? != "0" ]; then
>  echo -e "$CMK_HEADER\n2 $CMK_NAME - Postgres Output does not
> seem valid. Please check script $ME and output in $TMP_FILE" >
> $CMK_SPOOLDIR/$CMK_SPOOLFILE
>  exit 1;
> fi
>
> # CSV in Array einlesen
> IFS=$'\n' rea

Re: Scriptable way to validate a pg_dump restore ?

[Shaheed Haque]

On Tue, 30 Jan 2024, 05:02 Adrian Klaver,  wrote:

> On 1/29/24 11:35, Shaheed Haque wrote:
> >
> >
> > On Tue, 30 Jan 2024, 00:27 Adrian Klaver,  > > wrote:
> >
> > On 1/29/24 10:12, Shaheed Haque wrote:
> >
> >
> >  >
> >  > Yes. But I was under the impression that the initial copy of
> logical
> >  > replication was the same?
> >  >
> >
> > Are you taking about the copy_data option to WITH?
> >
> > If so yes and no.
> >
> > Yes as it uses COPY to transfer the data.
> >
> >
> > Yes, this is what I meant.
> >
> > No as what COPY transfers can be affected by WHERE clauses on the
> > publisher. Also if you have cascading publishers/subscriptions the
> > 'original' data maybe upstream of the publisher you are comparing to.
> >
> >
> > Good points, understood. For the next bit, let's assume neither of these
> > are in play.
> >
> > Finally logical replication is generally not static so there is the
> > issue of determining a point in time for the check.
> >
> >
> > Indeed. I currently have a static source db but would eventually like to
> > eliminate the implied downtime. What I'd like to provide my user is some
>
> Implied downtime of what?
>
> > indication of progress initially during the copy_data phase, and for the
> > future, of the anticipated incremental convergence.
> >
> > And, as per my other note, I would ideally like to be able to do this
> > using only a connection to one db.
> >
> > I was assuming that logical replication needed "something" similar
> > internally, and was hoping the LSNs were that "something".
>
> I'm going to say up front I am no expert on the internals of logical
> replication. Will point you at:
>
> https://www.postgresql.org/docs/current/protocol-message-formats.html
>
> A quick look at that indicates to me it is more involved then you think.
>

I'll take a look. Thanks for the tip and the gentle guidance; it is much
appreciated.


> >
> > Thanks, Shaheed
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Scriptable way to validate a pg_dump restore ?

[Shaheed Haque]

On Tue, 30 Jan 2024, 05:02 Adrian Klaver,  wrote:

> On 1/29/24 11:35, Shaheed Haque wrote:
> >
> >
> > On Tue, 30 Jan 2024, 00:27 Adrian Klaver,  > > wrote:
> >
> > On 1/29/24 10:12, Shaheed Haque wrote:
> >
> >
> >  >
> >  > Yes. But I was under the impression that the initial copy of
> logical
> >  > replication was the same?
> >  >
> >
> > Are you taking about the copy_data option to WITH?
> >
> > If so yes and no.
> >
> > Yes as it uses COPY to transfer the data.
> >
> >
> > Yes, this is what I meant.
> >
> > No as what COPY transfers can be affected by WHERE clauses on the
> > publisher. Also if you have cascading publishers/subscriptions the
> > 'original' data maybe upstream of the publisher you are comparing to.
> >
> >
> > Good points, understood. For the next bit, let's assume neither of these
> > are in play.
> >
> > Finally logical replication is generally not static so there is the
> > issue of determining a point in time for the check.
> >
> >
> > Indeed. I currently have a static source db but would eventually like to
> > eliminate the implied downtime. What I'd like to provide my user is some
>
> Implied downtime of what?
>

Oh, forgot to say: the downtime of my Django based app. Not anything at the
PG level.


> > indication of progress initially during the copy_data phase, and for the
> > future, of the anticipated incremental convergence.
> >
> > And, as per my other note, I would ideally like to be able to do this
> > using only a connection to one db.
> >
> > I was assuming that logical replication needed "something" similar
> > internally, and was hoping the LSNs were that "something".
>
> I'm going to say up front I am no expert on the internals of logical
> replication. Will point you at:
>
> https://www.postgresql.org/docs/current/protocol-message-formats.html
>
> A quick look at that indicates to me it is more involved then you think.
>
> >
> > Thanks, Shaheed
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Write Logical Replication Spill Files to different volume

[Klaus Darilion]

Hello!

If I understand it right, the logical replication .spill files in the 
pg_replslot subfolders are temporary files that need not survive server 
crashes (as on postgresql restart the WAL senders will delete old spill 
files, start again reading WAL and regenerate the spill files if 
necessary).


For big transactions with plenty of replication slots, the spill files 
may consume plenty of disk space. I think it would be useful to have the 
spill files on a different disk, ie the "normal" database files and the 
pg_replslot status file are on a high available volume (DRBD, shared 
storage ...) whereas the spill files are only on local disks.


Is this already possible? If not, are there reasons why that should not 
be done?


thanks
Klaus

Re: Good overview of programming IN Postgres?

[Joshua Kacsir]

Guyren,

On Tue, Jan 30, 2024 at 12:19 AM Guyren Howe  wrote:

> Is there a good overview of programming WITHIN Postgres? Not writing code
> in client languages that communicates WITH Postgres, but how to take full
> advantage of Postgres’ unique programming features — the advanced type
> system, the various PL languages, triggers, stored procedures, functions, …
>

While it uses Postgres 11 for its basis (though it looks like an update for
15 is in-progress), I find The Art of PostgreSQL by Dimitri Fontaine (
https://theartofpostgresql.com/) to be an excellent discussion for
"Postgres-First" development. Would recommend studying the release notes
for the versions of Postgres released after 11 to catch up on all the
improvements / new features added since it was written.

Best Regards,

Joshua Kacsir

-- 
Joshua Kacsir
Data Architect
www.crunchydata.com
joshua.kac...@crunchydata.com

[This email was written *without* the assistance of LLMs]