Re: php connection failure

‐‐‐ Original Message ‐‐‐ On Tuesday, August 10th, 2021 at 2:14 PM, Tom Lane wrote: > The postgres process is listening, but this has nothing to do with > > whether the kernel will allow any packets to arrive there. > Installed 'nftables'. According to the documentation (https://wiki.de

Re: php connection failure

‐‐‐ Original Message ‐‐‐ On Tuesday, August 10th, 2021 at 3:44 PM, Adrian Klaver wrote: > 1. There is another local line with peer that you missed. > 2. You changed the wrong pg_hba.conf file. > Frow within postgresql 'psql' terminal: SHOW hba_file; /etc/postgresql/9.6/main/

Re: JWT decoder

Masih Tavassoli wrote: > But there doesn't seem to be a way doing it in postgres. > Has anyone got any suggesions? RFC 7519 says: A JWT is represented as a sequence of URL-safe parts separated by period ('.') characters. Each part contains a base64url-encoded value. The number

Re: php connection failure

Hello, On Wed, 2021-08-11 at 11:09 +, ourdiaspora wrote: > > > The connection to postgresql seems OK now (thank you), but the php > web page continues to fail to connect to the database: > > " > > > Generic CPAC database > > >    

Getting pg_stat_database data takes significant time

Hi, We have servers where there is single app db, but one that contains MANY schema/tables. This is on Pg 12.6. Simple query like: select * from pg_stat_database where datname = 'app_name' can take up to 800ms! #v+ QUERY PLAN

Re: Getting pg_stat_database data takes significant time

Just taking a shot, as I have seen in some previous issues? Ignore is not relevant. Can you run vacuum on pg_class and check the query again , or do you see pg_class bloated ? The other option would be gdb backtrace I think that would help.

Re: Getting pg_stat_database data takes significant time

On Wed, Aug 11, 2021 at 06:52:15PM +0530, Vijaykumar Jain wrote: > Just taking a shot, as I have seen in some previous issues? Ignore is not > relevant. > > Can you run vacuum on pg_class and check the query again , or do you see > pg_class bloated ? pg_class is large, but vacuuming it didn't h

Re: Getting pg_stat_database data takes significant time

On Wed, 11 Aug 2021 at 18:59, hubert depesz lubaczewski wrote: > On Wed, Aug 11, 2021 at 06:52:15PM +0530, Vijaykumar Jain wrote: > > Just taking a shot, as I have seen in some previous issues? Ignore is > not > > relevant. > > > > Can you run vacuum on pg_class and check the query again , or d

Re: php connection failure

ourdiaspora writes: > Installed 'nftables'. If you didn't have nftables installed already, then it's likely that packet filtering is being controlled by some other userland API. Can't help you much on what that is. regards, tom lane

Re: Getting pg_stat_database data takes significant time

On Wed, 11 Aug 2021 at 19:12, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > >> ok my guess here was, since pg_class is updated every now and then with > stats, it might require some lock while adding the data. > so if it were bloated, that would block the planner to get the estimates

Re: php connection failure

On 8/11/21 4:09 AM, ourdiaspora wrote: ‐‐‐ Original Message ‐‐‐ For sign-in today via commands below, the log file shows nothing new, only the old errors: " psql -d cpacweb -U cpacapsql (9.6.16) Type "help" for help. cpacweb=> \q psql -d cpacweb -h localhost -U cpaca Password for user

Re: php connection failure

On 8/11/21 6:10 AM, rob stone wrote: Hello, Why are you starting postgres with a -c option and no parameters following? I see: /usr/lib/postgresql/9.6/bin/postgres -D /var/lib/postgresql/9.6/main -c config_file=/etc/postgresql/9.6/main/postgresql.conf This is standard for the Ubuntu pac

Re: php connection failure

On 8/11/21 7:09 AM, Adrian Klaver wrote: On 8/11/21 4:09 AM, ourdiaspora wrote: ‐‐‐ Original Message ‐‐‐ For sign-in today via commands below, the log file shows nothing new, only the old errors: " psql -d cpacweb -U cpacapsql (9.6.16) Type "help" for help. cpacweb=> \q psql -d cpacw

Re: Getting pg_stat_database data takes significant time

Two things, 1. this depends on reading the stats file; that's done once per transaction. So if you run the query twice in a transaction, the second time will take less time. You can know how much time is spent reading that file by subtracting both times. 2. EXPLAIN (VERBOSE) will tell you which

Re: Getting pg_stat_database data takes significant time

On Wed, Aug 11, 2021 at 10:16:13AM -0400, Alvaro Herrera wrote: > 1. this depends on reading the stats file; that's done once per > transaction. So if you run the query twice in a transaction, the second > time will take less time. You can know how much time is spent reading > that file by subtra

Re: php connection failure

‐‐‐ Original Message ‐‐‐ On Wednesday, August 11th, 2021 at 2:10 PM, rob stone wrote: > > the call to pg_connect as a try . . . catch block so that the exact > Sorry but do not understand "try catch block" > > Are you sure postgres is configured to use port 5432? No, but the impres

Re: php connection failure

On Wednesday, August 11th, 2021 at 3:09 PM, Adrian Klaver wrote: > > Is the Web server/PHP app on the same machine as the Postgres server? > All programs about this question are installed on a single computer; the intention is to test and learn first on a local machine. > If it is on the sa

Re: (13.1) pg_basebackups ./. pg_verifybackup

El día martes, agosto 10, 2021 a las 11:38:57a. m. +0200, Matthias Apitz escribió: > I think, I sorted it out by doing this: > > I moved away the tar-archives: > > $ cd /data/postgresql133/backup-20210810-1 > $ mkdir ../saved > $ mv *.tar.gz ../saved > > I unpacked 'base.tar.gz' the usual way:

Re: (13.1) pg_basebackups ./. pg_verifybackup

> > > Could some kind sol acknowledge me that this is the correct procedure to > use pg_verifybackup? Thanks in advance > > postgres@db:~/playground/demo$ initdb -D db 2>/dev/null 1>&2 postgres@db:~/playground/demo$ pg_ctl -D db -l logfile start 2>/dev/null 1>&2 postgres@db:~/playground/demo$ psql

Re: (13.1) pg_basebackups ./. pg_verifybackup

btw, you can also use pgbackrest for backups. i'll let docs do the talking, pgBackRest - Reliable PostgreSQL Backup & Restore but it can help you manage incremental and differential and full backups along with parallel support. (so fast and less bandwidth) also a quick on

Re: (13.1) pg_basebackups ./. pg_verifybackup

El día jueves, agosto 12, 2021 a las 12:47:32a. m. +0530, Vijaykumar Jain escribió: > > > > > > Could some kind sol acknowledge me that this is the correct procedure to > > use pg_verifybackup? Thanks in advance > > > > > ... > >postgres@db:~/playground/demo/basebackup$ tar xzf base.tar.gz >

Re: php connection failure

On 8/11/21 10:02 AM, ourdiaspora wrote: On Wednesday, August 11th, 2021 at 3:09 PM, Adrian Klaver wrote: Is the Web server/PHP app on the same machine as the Postgres server? All programs about this question are installed on a single computer; the intention is to test and learn first on

Re: (13.1) pg_basebackups ./. pg_verifybackup

On Thu, 12 Aug 2021 at 01:48, Matthias Apitz wrote: > This is exactly the point of my question (and I figured it out too): > Where is this explained that «pg_wal.tar.gz file has to uncompressed in > pg_wal dir»? > > indeed, I am not able to find or search relative reference in docs (i never used

Re: (13.1) pg_basebackups ./. pg_verifybackup

On 8/11/21 1:18 PM, Matthias Apitz wrote: El día jueves, agosto 12, 2021 a las 12:47:32a. m. +0530, Vijaykumar Jain escribió: Could some kind sol acknowledge me that this is the correct procedure to use pg_verifybackup? Thanks in advance ... postgres@db:~/playground/demo/basebackup$

ERROR: invalid memory alloc request size when committing transaction

Hello Experts We have a large-ish (16T) database cluster which were are performing the following sequence on. - First we upgrade the whole cluster from pg11 to pg13, using pg_upgrade (this succeeds) - Next we run a migration script on each database in the cluster. The migration script

Serious Assistance with PostgreSQL True Infinite Arbitrary Precision Maths.

Dear pgsql-general, I'm after Infinite Precision Integer (ii) or Infinite Precision Rational (ir) type and function support for PostgreSQL, as an easy installation for two new native types. That are available under free and open source conditions, for public, private and commercial, free use. I

Re: ERROR: invalid memory alloc request size when committing transaction

Michael Harris writes: > - Next we run a migration script on each database in the cluster. The > migration script > converts a large number of tables from inheritance based partitioning to > declarative > partitioning. Unfortunately I am not at liberty to share the migration > script

RE: ERROR: invalid memory alloc request size when committing transaction

Thanks Tom, > How many is "a large number"? 377k approx. I thought we had logged all the SQL statements last time we ran this, but we forgot to set log_statement. We'll do that next time so we can get a figure on exactly how many of different types of statement are in the transaction. > Can y