monitor health of native logical replication

[Rory Campbell-Lange]

Based on my initial testing native logical replication seems easier and faster
to setup than pglogical, and may be easier for our team to administer.

One sticking point is monitoring the health of native logical replication.

Are there some native logical replication functions or calls that can, for
example, provide info such as the function pglogical.show_subscription_status?

if (pglogical_worker_running(apply))
{
PGLogicalSyncStatus*sync;
sync = get_subscription_sync_status(sub->id, true);

if (!sync)
status = "unknown";
else if (sync->status == SYNC_STATUS_READY)
status = "replicating";
else
status = "initializing";
}
else if (!sub->enabled)
status = "disabled";
else
status = "down";

The native pg_stat_subscription view does not show, for example, if the
subscription is down.

Rory

Feature request: psql --idle

[Wiwwo Staff]

Since changing ph_hda.conf file to give users access involves the restart
of server, many companies I work(ed) use a bastion host, where users ssh
to, and are allowed "somehow" use postgresql.

Still, those users need some login shell.

It would be great to give them psql as a login shell (in /etc/passwd).
But doing so, will result in psql exiting with error with the usual
$ psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No
such file or directory
Is the server running locally and accepting connections on that socket?

What would help, is a --idle option, where psql does not exit, stays idle
and waits for user to give a \conn command.
Something similar to
sqlplus /nolog

Is anything like that feasible or is there another solution/workaround?

Tnx!

Re: Feature request: psql --idle

[Mateusz Henicz]

Hi,
Your first sentence is wrong. Changing pg_hba.conf file does not require
server restart. It is enough to reload the configuration using "pg_ctl
reload", "select pg_reload_conf();" in psql or just sending SIGHUP from
linux terminal to postmaster process after changing the pg_hba file.

To achieve something like this you could use simple bash script like the
one below and add it to your /etc/passwd file, like
/etc/passwd
test:x:1001:1001::/home/test:/home/test/skrypt.sh

 /home/test/skrypt.sh
#!/bin/bash
echo "Select a database"
echo "1. local"
echo "2. other_dbs"

read dbname

if [ "$dbname" == "1" ] || [ "$dbname" == "local" ]
then
  psql -h 127.0.0.1 -p 5432 -U postgres postgres
elif  [ "$dbname" == "2" ] || [ "$dbname" == " other_dbs  " ]
then
  psql -h 127.0.0.1 -p  -U postgres postgres
fi

Hope it helps.

Cheers,
Mateusz

śr., 27 lip 2022 o 14:50 Wiwwo Staff  napisał(a):

> Since changing ph_hda.conf file to give users access involves the restart
> of server, many companies I work(ed) use a bastion host, where users ssh
> to, and are allowed "somehow" use postgresql.
>
> Still, those users need some login shell.
>
> It would be great to give them psql as a login shell (in /etc/passwd).
> But doing so, will result in psql exiting with error with the usual
> $ psql
> psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed:
> No such file or directory
> Is the server running locally and accepting connections on that socket?
>
> What would help, is a --idle option, where psql does not exit, stays idle
> and waits for user to give a \conn command.
> Something similar to
> sqlplus /nolog
>
> Is anything like that feasible or is there another solution/workaround?
>
> Tnx!
>

Re: Feature request: psql --idle

[Julien Rouhaud]

Hi,

On Wed, Jul 27, 2022 at 02:49:45PM +0200, Wiwwo Staff wrote:
> Since changing ph_hda.conf file to give users access involves the restart
> of server, many companies I work(ed) use a bastion host, where users ssh
> to, and are allowed "somehow" use postgresql.

You mean pg_hba.conf right?  It doesn't need a restart, only a reload as
documented at https://www.postgresql.org/docs/current/auth-pg-hba-conf.html:

>The pg_hba.conf file is read on start-up and when the main server process
>receives a SIGHUP signal. If you edit the file on an active system, you will
>need to signal the postmaster (using pg_ctl reload, calling the SQL function
>pg_reload_conf(), or using kill -HUP) to make it re-read the file.

That being said, it's usually not a good idea to allow connection from all
around the world, so not all users may be able to connect from their local
machine anyway.

> What would help, is a --idle option, where psql does not exit, stays idle
> and waits for user to give a \conn command.
> Something similar to
> sqlplus /nolog
> 
> Is anything like that feasible or is there another solution/workaround?

That might be a good thing to have, as some users may want to rely on psql for
things like \h or \? to work on stuff while not being able to connect to a
remote server (and for some reason who wouldn't want to, or maybe couldn't,
install a local instance).  I would call it something like "--no-connection"
more than "--idle" though.

Performance issue on GIN index with gin_trgm_ops index column

[Lars Vonk]

 Hi all,

 We are investigating a performance issue with searching on a GIN with
gin_trgm_ops indexed column. This specific (organization_id,aggregate_type)
has in total 19K records and the (organization_id) has in total 30K
records. The search record table has in total 38M records.

 The table and index are defined as follows:

   Column  |   Type| Collation | Nullable |
  Default

-+---+---+--+-
   id  | bigint|   | not null |
nextval('search_records_id_seq'::regclass)
   organization_id | uuid  |   | not null |
   aggregate_id| uuid  |   | not null |
   aggregate_type  | character varying |   | not null |
   document| text  |   |  |
  Indexes:
  "search_records_pkey" PRIMARY KEY, btree (id)
  "search_records_keys" UNIQUE, btree (organization_id, aggregate_id,
aggregate_type)
  "search_records_btree_gin" gin ((organization_id::character varying),
aggregate_type, document gin_trgm_ops)

The query we execute is:

  select aggregate_id from search_records where organization_id::varchar =
'975097c5-e760-4603-9236-fcf2e8580a7c' and aggregate_type = 'FooRecord' and
document ilike '%user.n...@gmail.com%';

Resulting in the following plan:

   Bitmap Heap Scan on search_records  (cost=2184.00..2188.02 rows=1
width=104) (actual time=4332.007..4332.008 rows=1 loops=1)
 Recheck Cond: organization_id)::character varying)::text =
'975097c5-e760-4603-9236-fcf2e8580a7c'::text) AND ((aggregate_type)::text =
'FooRecord'::text) AND (document ~~* '%user.n...@gmail.com%'::text))
 Heap Blocks: exact=1
 Buffers: shared hit=23920 read=9752
 I/O Timings: read=4017.360
 ->  Bitmap Index Scan on search_records_btree_gin  (cost=0.00..2184.00
rows=1 width=0) (actual time=4331.987..4331.987 rows=1 loops=1)
   Index Cond: organization_id)::character varying)::text =
'975097c5-e760-4603-9236-fcf2e8580a7c'::text) AND ((aggregate_type)::text =
'FooRecord'::text) AND (document ~~* '%user.n...@gmail.com%'::text))
   Buffers: shared hit=23920 read=9751
   I/O Timings: read=4017.355
   Planning Time: 0.268 ms
   Execution Time: 4332.030 ms
  (11 rows)

We are running on Postgres RDS with engine version 12.8 with 32GB memory
and 8GB shared_buffer. We have 442GB of 2000GB diskspace left.

Sometimes we also have queries for this particular customer which take more
than 20 seconds. The content of the document in those cases are similar to:

   User Name Kees postgresstreet Amsterdam 1000 AA user.n...@gmail.com 1234

Are we doing something wrong? I find the I/O timings quite high, does this
mean that it took 4000MS to read the 9752 blocks from the disk?

Any other tips and or suggestions are welcome.

Kind regards,
Lars Vonk

Re: Feature request: psql --idle

[Michael Nolan]

On Wed, Jul 27, 2022 at 7:50 AM Wiwwo Staff  wrote:

> Since changing ph_hda.conf file to give users access involves the restart
> of server, many companies I work(ed) use a bastion host, where users ssh
> to, and are allowed "somehow" use postgresql.
>
> Still, those users need some login shell.
>
>
No, they don't need login shells.  You can set up an SSH tunnel to the
bastion server on the user's system that in turn sets up a tunnel to the
database server on the bastion server.

Something like this:
ssh -f  -N user@bastion -L :dbserver:

So when the user connects to port  on the local server it tunnels
through to port  on the dbserver through the bastion server.

This way you can limit who has access to the bastion server, and you can
set the PostgreSQL server to accept (only) the IP address of the bastion
server.We use this to access a database on an RDS server at AWS from a
server at a different data center.
--
Mike Nolan

Re:

[Alicja Kucharczyk]

śr., 27 lip 2022 o 08:08 hubert depesz lubaczewski 
napisał(a):

> On Tue, Jul 26, 2022 at 10:48:47AM -0700, Adrian Klaver wrote:
> > On 7/26/22 9:29 AM, Ron wrote:
> > > On 7/26/22 10:22, Adrian Klaver wrote:
> > > > On 7/26/22 08:15, Rama Krishnan wrote:
> > > > > Hi Adrian
> > > > >
> > > > >
> >
> > > > > What is size of table?
> > > > >
> > > > > I m having two Database example
> > > > >
> > > > > 01. Cricket 320G
> > > > > 02.badminton 250G
> > > >
> > > > So you are talking about an entire database not a single table,
> correct?
> > >
> > > In a private email, he said that this is what he's trying:
> > > Pg_dump -h endpoint -U postgres Fd - d cricket | aws cp -
> > > s3://dump/cricket.dump
> > >
> > > It failed for obvious reasons.
> > From what I gather it did not fail, it just took a long time. Not sure
> > adding -j to the above will improve things, pretty sure the choke point
> is
> > still going to be aws cp.
>
> It's really hard to say what is happening, because the command, as shown
> wouldn't even work.
>
> Starting from Pg_dump vs. pg_dump, space between `-` and `d`, "Fd" as
> argument, or even the idea that you *can* make -Fd dumps to stdout and
> pass it to aws cp.
>
> depesz
>

I believe it's worth to look at this project:
https://github.com/dimitri/pgcopydb since it is trying to solve exactly
this problem


pozdrawiam,
best regards,
mit freundlichen Grüßen,
Alicja Kucharczyk
*Warsaw PostgreSQL User Group*