Re: Linux Update Experience

[Peter J. Holzer]

On 2020-05-28 14:36:34 +, Zwettler Markus (OIZ) wrote:
> I'm not talking about this specific bug or its resolution.
> 
> I want to talk about the Linux update problem in general.
> 
> Anyone updating Linux might get such nerving dependency troubles.

In my experience (having administrated Linux servers for 25 years),
dependency troubles outside of major updates are rare. But of course if
you do that long enough with enough different systems you will run into
one sooner or later.

Some ways to minimize the frequency of such troubles:

* Use a base distribution with a lot of packages. RHEL has a lot fewer
  packages than Debian, so when we used RHEL (we still have a few
  servers) we used external repositories (RPMForge, EPEL, ...) a lot
  more. Some of those external repos may not be as well maintained as
  the base distro and of course they may not coordinate with each other.
  So more external repos mean a higher risk of conflicts.

* Use specialized systems. In the 90's servers were big and expensive,
  so we had few of them and each was running a lot of different
  services. Planning an upgrade took months. These days we use (mostly)
  VMs, each of which is running only one service. That greatly reduces
  the number or packages installed and the number of external repos,
  thus reducing the potential for conflicts.

* Update frequently. That reduces the risk of needing a package which
  has since been deleted from a repo, but more importantly it makes it
  easier to pinpoint the cause of a conflict.

When a conflict does occur. being familiar with the packaging system
helps a lot. Sometimes just uninstalling a few packages helps. Sometimes
something in a repo has changed and you need to change the configuration
to match (as has apparently happened here), so being on relevant
announce-lists of having the URL of the repo website handy helps.
Sometimes you can force installation (althought that will often cause
problems later). In some cases I built my own packages.

hp


-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

PG server process can keep some info of backend

[brajmohan saxena]

Hi,

Is there any extension or option in PG to keep information of any (
memory context/some memory address) of backend process in sever/postmaster
main process.

Thanks
Braj

Re: Linux Update Experience

[Christoph Moench-Tegeder]

## Peter J. Holzer (hjp-pg...@hjp.at):

> * Update frequently. That reduces the risk of needing a package which
>   has since been deleted from a repo, but more importantly it makes it
>   easier to pinpoint the cause of a conflict.

This. Plus: make sure you can re-create any machine in a fully deterministic
manner - that way, you can easily create a test environment to match
production (minus RAM/CPU/storage) for testing upgrades beforehand.

Rationale: experience shows that using Test as "first stage" and carrying
changes forward to Production results in a "contaminated" test environment;
before long, results of failed experiments have accumulated on Test,
Production and Test are diverging, and at that point Test has lost it's
purpose.
(For some people, that's a point for containerization: you don't change
a running container, but package a new one. Other environments have so
much Production with all the redundancy etc that they can "test in
production" and just scrap-and-replace failed tests, but that's not an
option if you have just a handful of systems.)

Regards,
Christoph

-- 
Spare Space

Re: Slow SELECT

[Frank Millman]

On 2020-05-26 12:04 PM, David Rowley wrote:


Since "my_table" is small, you'd likely be much better doing a manual
rewrite of the query to join a subquery containing the required
details from "my_table".  It looks like you want the row_id from the
latest tran_date for each fld_N column. So something like:

SELECT a.row_id
FROM table_1 b
CROSS JOIN table_2 c
CROSS JOIN table_3 d
CROSS JOIN table_4 e
LEFT OUTER JOIN (
SELECT fld_1,fld_2,fld_3,fld_4,row_id,tran_date,
ROW_NUMBER() OVER (PARTITION BY fld_1,fld_2,fld_3,fld_4 ORDER BY
tran_date DESC) row_num
FROM my_table
WHERE deleted_id = 0
) a ON a.fld_1 = b.row_id AND a.fld_2 = c.row_id AND a.fld_3 =
d.row_id AND a.fld_4 = e.row_id AND a.row_num = 1;

Should do it. You could also perhaps do something with DISTINCT ON
instead of using ROW_NUMBER(). That might be a bit more efficient, but
it's unlikely to matter too much since there are only 167 rows in that
table.



I have studied the above SELECT, and I now more or less understand it. I 
would not have come up with that unaided, so many thanks.


I tried DISTINCT ON, and it was very efficient, but unfortunately that 
is not supported by SQL Server or sqlite3.


Then I came up with this alternative, which works on all three platforms 
and seems a bit faster than the above -


SELECT a.row_id FROM (
SELECT row_id,
ROW_NUMBER() OVER (PARTITION BY fld_1, fld_2, fld_3, fld_4
ORDER BY tran_date DESC) row_num
FROM my_table
WHERE deleted_id = 0
   ) as a
WHERE a.row_num = 1

Do you see any problem with this?

Thanks

Frank

lib/libecpg.so.6.11 && valgrind

[Matthias Apitz]

Hello,

We're getting to the finish line in the port of our servers to
PostgreSQL 11.4. and started valgrind'ing the code, as the PostgreSQL is
new in the servers. Of course we have our own homework to do, but there
are also complaints of valgrind pointing to the lib/libecpg.so.6.11, for
example:

==9773== 9 bytes in 1 blocks are still reachable in loss record 3 of 52
==9773==at 0x4C2E2E3: malloc (vg_replace_malloc.c:299)
==9773==by 0x1439F749: strdup (in /lib64/libc-2.26.so)
==9773==by 0x146DF448: ecpg_strdup (in 
/usr/local/sisis-pap/pgsql/lib/libecpg.so.6.11)
==9773==by 0x146E048E: ECPGconnect (in 
/usr/local/sisis-pap/pgsql/lib/libecpg.so.6.11)
==9773==by 0xB03E846: DB_opdbP (dbcall.pgc:794)
==9773==by 0xB03DFDB: DB_opdb (dbcall.pgc:556)
==9773==by 0x5CC22F2: EC_General::OpenDatabase(_SQL_EXCL) (eC_general.C:121)
==9773==by 0x40AEB8: SlnpInitDaemon (ACQDaemon.C:644)
==9773==by 0x40A878: main (ACQDaemon.C:213)

How the PostgreSQL community likes us to deal with this?

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

problem with self built postgres 9.0.9

[Gabriele Bulfon]

Hi, I had to build postgres 9.0.9 from sources to run it under our new 
XStreamOS/illumos release.
It used to build and run without problems in previous releases.
 
On this, just by running initdb I get these errors:
sonicle@xstorage1:/sonicle$ initdb -D /sonicle/pgdata
The files belonging to this database system will be owned by user "sonicle".
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".
creating directory /sonicle/pgdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /sonicle/pgdata/base/1 ... ok
initializing pg_authid ... FATAL: column is not in index
child process exited with exit code 1
initdb: removing data directory "/sonicle/pgdata"
 
Even running postgres over an already previously populated pgdata, any attempt 
to connect to the database results in "
FATAL: column is not in index".
 
Any idea where the problem may be?
 
Thanks!
Gabriele
 
 
Sonicle S.r.l. 
: 
http://www.sonicle.com
Music: 
http://www.gabrielebulfon.com
Quantum Mechanics : 
http://www.cdbaby.com/cd/gabrielebulfon

Re: problem with self built postgres 9.0.9

[Adrian Klaver]

On 5/29/20 6:32 AM, Gabriele Bulfon wrote:
Hi, I had to build postgres 9.0.9 from sources to run it under our new 
XStreamOS/illumos release.


First 9.0 is ~ 5 years past EOL and it ended at 9.0.23.

Second this is as close as I could come to a solution:

https://www.postgresql.org/message-id/17948.1365090217%40sss.pgh.pa.us

Given that you say you are building against a new OS release I would 
look at what changed in the development stack.




It used to build and run without problems in previous releases.
On this, just by running initdb I get these errors:

sonicle@xstorage1:/sonicle$ initdb -D /sonicle/pgdata
The files belonging to this database system will be owned by user "sonicle".
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".

creating directory /sonicle/pgdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /sonicle/pgdata/base/1 ... ok
initializing pg_authid ... FATAL: column is not in index
child process exited with exit code 1
initdb: removing data directory "/sonicle/pgdata"
Even running postgres over an already previously populated pgdata, any 
attempt to connect to the database results in "FATAL: column is not in 
index".

Any idea where the problem may be?
Thanks!
Gabriele

*Sonicle S.r.l. *: http://www.sonicle.com 
*Music: *http://www.gabrielebulfon.com 
*Quantum Mechanics : *http://www.cdbaby.com/cd/gabrielebulfon



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: problem with self built postgres 9.0.9

[Gabriele Bulfon]

Amazing! Rebuilt without -O and it worked like a charm!
Thanks, at the moment I need to stick to 9.0.9 on this machine to be able to 
reuse the same database files.
 
Thanks!
Gabriele
 
 
Sonicle S.r.l. 
: 
http://www.sonicle.com
Music: 
http://www.gabrielebulfon.com
Quantum Mechanics : 
http://www.cdbaby.com/cd/gabrielebulfon
--
Da: Adrian Klaver
A: Gabriele Bulfon
pgsql-general@lists.postgresql.org
Data: 29 maggio 2020 15.45.32 CEST
Oggetto: Re: problem with self built postgres 9.0.9
On 5/29/20 6:32 AM, Gabriele Bulfon wrote:
Hi, I had to build postgres 9.0.9 from sources to run it under our new
XStreamOS/illumos release.
First 9.0 is ~ 5 years past EOL and it ended at 9.0.23.
Second this is as close as I could come to a solution:
https://www.postgresql.org/message-id/17948.1365090217%40sss.pgh.pa.us
Given that you say you are building against a new OS release I would
look at what changed in the development stack.
It used to build and run without problems in previous releases.
On this, just by running initdb I get these errors:
sonicle@xstorage1:/sonicle$ initdb -D /sonicle/pgdata
The files belonging to this database system will be owned by user "sonicle".
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".
creating directory /sonicle/pgdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /sonicle/pgdata/base/1 ... ok
initializing pg_authid ... FATAL: column is not in index
child process exited with exit code 1
initdb: removing data directory "/sonicle/pgdata"
Even running postgres over an already previously populated pgdata, any
attempt to connect to the database results in "FATAL: column is not in
index".
Any idea where the problem may be?
Thanks!
Gabriele
*Sonicle S.r.l. *: http://www.sonicle.com
*Music: *http://www.gabrielebulfon.com
*Quantum Mechanics : *http://www.cdbaby.com/cd/gabrielebulfon
--
Adrian Klaver
adrian.kla...@aklaver.com

Re: problem with self built postgres 9.0.9

[David G. Johnston]

On Fri, May 29, 2020 at 7:08 AM Gabriele Bulfon  wrote:

> Amazing! Rebuilt without -O and it worked like a charm!
> Thanks, at the moment I need to stick to 9.0.9 on this machine to be able
> to reuse the same database files.
>
>
Just to be thorough.  You can update to 9.0.23 (i.e., build against the tip
of the 9.0.x set of branches) and still use the same database files.  For
all versions (starting with v10 the version has only two components, not
three) changing the final digit in the version is a code-only change.

There is no material difference to risk for building 9.0.23 against the
newer O/S and compiler, etc, than it is to build 9.0.9 against the newer
O/S and compiler, etc.  You assumed basically maximum risk when you choose
to keep using version 9.0 and upgraded everything else around it to
versions that were possibly never tested against it - and if they were
tested it is more likely they were tested against 9.0.23 as it is years
more current.

David J.

Re: problem with self built postgres 9.0.9

[Tom Lane]

Gabriele Bulfon  writes:
> Amazing! Rebuilt without -O and it worked like a charm!

Yeah, modern compilers tend to apply optimizations that break old versions
of Postgres.  -O0 usually takes care of it.

regards, tom lane

pg_ctl can't start db server

[Tanja Savic]

Hi,

We did the upgrade of PostgreSQL 10 to 12 on linux server. Everything was fine 
(can connect via pgAdmin, data is there...) except two tablespaces which are 
still in directory for PostgreSQL 10.
So we followed these steps to move tablespaces: 
https://www.enterprisedb.com/blog/moving-tablespaces-postgresql
The problem was at the end while executing:
$ pg_ctl start

This is the error trace:

pg_ctl: another server might be running; trying to start server anyway
waiting for server to startpostgres: could not access the server 
configuration file "/var/lib/postgresql/12/main/postgresql.conf": No such file 
or directory
stopped waiting
pg_ctl: could not start server
Examine the log output.

The configuration file path is /etc/postgresql/12/main/ postgresql.conf and it 
is configured in /var/lib/postgresql/12/main/postmaster.opts:

"/usr/lib/postgresql/12/bin/postgres "-D" "/var/lib/postgresql/12/main" "-c" 
"config_file=/etc/postgresql/12/main/postgresql.conf"

Why does pg_ctl complains about the postgresql.conf path?


Thank you,
Tanja

Re: pg_ctl can't start db server

[Tom Lane]

Tanja Savic  writes:
> The configuration file path is /etc/postgresql/12/main/ postgresql.conf and 
> it is configured in /var/lib/postgresql/12/main/postmaster.opts:

> "/usr/lib/postgresql/12/bin/postgres "-D" "/var/lib/postgresql/12/main" "-c" 
> "config_file=/etc/postgresql/12/main/postgresql.conf"

> Why does pg_ctl complains about the postgresql.conf path?

I don't remember the exact interaction between -D and an explicit
config_file setting, but evidently it's not working the way you wish.
See

https://www.postgresql.org/docs/current/runtime-config-file-locations.html

which suggests that you should use -D to point at the directory where
postgresql.conf is, and set the data directory with a parameter within
postgresql.conf.

(Note that pg_ctl probably only understands this way, even though
theoretically the postmaster can deal with other approaches to
setting up an external config file.)

regards, tom lane

Audit Role Connections

[Chris Morris]

We're using Heroku's PG, and it comes with a default, almost super-user.
I'm wanting to restrict that account for only our production app servers,
and have anyone connecting for personal queries to go through an assigned
role by employee (that's all setup and working).

Other than polling pg_stat_activity (which isn't 100% accurate depending on
timing), is there a good way to audit connections? To detect which roles
are being used for connections?

Re: pg_ctl can't start db server

[Adrian Klaver]

On 5/29/20 8:19 AM, Tanja Savic wrote:

Hi,

We did the upgrade of PostgreSQL 10 to 12 on linux server. Everything 
was fine (can connect via pgAdmin, data is there…) except two 
tablespaces which are still in directory for PostgreSQL 10.


So we followed these steps to move tablespaces: 
https://www.enterprisedb.com/blog/moving-tablespaces-postgresql


The problem was at the end while executing:

$ pg_ctl start

This is the error trace:

pg_ctl: another server might be running; trying to start server anyway

waiting for server to startpostgres: could not access the server 
configuration file "/var/lib/postgresql/12/main/postgresql.conf": No 
such file or directory


stopped waiting

pg_ctl: could not start server

Examine the log output.

The configuration file path is /etc/postgresql/12/main/ postgresql.conf 
and it is configured in /var/lib/postgresql/12/main/postmaster.opts:


“/usr/lib/postgresql/12/bin/postgres "-D" "/var/lib/postgresql/12/main" 
"-c" "config_file=/etc/postgresql/12/main/postgresql.conf"


Why does pg_ctl complains about thepostgresql.conf path?


I'm guessing you are on Debian/Ubuntu?

If so what does the below show?:

pg_lsclusters

If it shows clusters then do:

sudo pg_ctlcluster 12 main start


If the above is not correct, then what user where you doing:

pg_ctl start

as?




Thank you,

Tanja




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Audit Role Connections

[Peter J. Holzer]

On 2020-05-29 12:42:47 -0500, Chris Morris wrote:
> We're using Heroku's PG,
[...]
> Other than polling pg_stat_activity (which isn't 100% accurate depending on
> timing), is there a good way to audit connections? To detect which roles are
> being used for connections?

Do you have access to the log files?

If you log_connections is on, you get messages like these:


2020-05-29 21:00:02 CEST [27995]: [2-1] user=w*,db=wds,pid=27995 LOG:  
connection authorized: user=w* database=wds
2020-05-29 21:00:18 CEST [27995]: [9-1] user=w*,db=wds,pid=27995 LOG:  
disconnection: session time: 0:00:15.979 user=w* database=wds host=[local]
2020-05-29 21:07:14 CEST [7481]: [2-1] user=u*,db=wds,pid=7481 LOG:  
connection authorized: user=u* database=wds
2020-05-29 21:07:14 CEST [7481]: [7-1] user=u*,db=wds,pid=7481 LOG:  
disconnection: session time: 0:00:00.016 user=u* database=wds host=[local]
2020-05-29 21:10:56 CEST [13918]: [2-1] user=m***,db=wds,pid=13918 LOG:  
connection authorized: user=m*** database=wds SSL enabled 
(protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256, 
compression=off)
2020-05-29 21:10:56 CEST [13918]: [11-1] user=m***,db=wds,pid=13918 LOG:  
disconnection: session time: 0:00:00.117 user=m*** database=wds 
host=143.130.**.** port=54037

(user names and IP addresses censored for privacy reasons)

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Audit Role Connections

[Chris Morris]

Ah, I do appear to have that enabled (inside Heroku's config), but I can't
find anything like that in the logs, so I've opened a ticket with them. Thx
a lot!

On Fri, May 29, 2020 at 2:25 PM Peter J. Holzer  wrote:

> On 2020-05-29 12:42:47 -0500, Chris Morris wrote:
> > We're using Heroku's PG,
> [...]
> > Other than polling pg_stat_activity (which isn't 100% accurate depending
> on
> > timing), is there a good way to audit connections? To detect which roles
> are
> > being used for connections?
>
> Do you have access to the log files?
>
> If you log_connections is on, you get messages like these:
>
>
> 2020-05-29 21:00:02 CEST [27995]: [2-1] user=w*,db=wds,pid=27995 LOG:
> connection authorized: user=w* database=wds
> 2020-05-29 21:00:18 CEST [27995]: [9-1] user=w*,db=wds,pid=27995 LOG:
> disconnection: session time: 0:00:15.979 user=w* database=wds
> host=[local]
> 2020-05-29 21:07:14 CEST [7481]: [2-1] user=u*,db=wds,pid=7481 LOG:
> connection authorized: user=u* database=wds
> 2020-05-29 21:07:14 CEST [7481]: [7-1] user=u*,db=wds,pid=7481 LOG:
> disconnection: session time: 0:00:00.016 user=u* database=wds
> host=[local]
> 2020-05-29 21:10:56 CEST [13918]: [2-1] user=m***,db=wds,pid=13918
> LOG:  connection authorized: user=m*** database=wds SSL enabled
> (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256,
> compression=off)
> 2020-05-29 21:10:56 CEST [13918]: [11-1] user=m***,db=wds,pid=13918
> LOG:  disconnection: session time: 0:00:00.117 user=m*** database=wds
> host=143.130.**.** port=54037
>
> (user names and IP addresses censored for privacy reasons)
>
> hp
>
> --
>_  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"
>