Re: Removing oids with pg_repack

[Achilleas Mantzios]

Στις 21/11/23 20:41, ο/η CG έγραψε:
I have a very large PostgreSQL 9.5 database that still has very large 
tables with oids. I'm trying to get rid of the oids with as little 
downtime as possible so I can prep the database for upgrade past 
PostgreSQL 11. I had a wild idea to mod pg_repack to write a new table 
without oids. I think it almost works.


To test out my idea I made a new table wipe_oid_test with oids. I 
filled it with a few rows of data.


But PostgreSQL still thinks that the table has oids:

mydata=# \d+ wipe_oid_test
                   Table "public.wipe_oid_test"
 Column | Type | Modifiers | Storage  | Stats target | Description
+--+---+--+--+-
 k      | text | not null  | extended |              |
 v      | text |           | extended |              |
Indexes:
    "wipe_oid_test_pkey" PRIMARY KEY, btree (k)
Has OIDs: yes
Except where does it mention in the pg_repack docs (or source) that it 
is meant to be used for NO OIDS conversion ?
I can modify pg_class and set relhasoids = false, but it isn't 
actually eliminating the oid column. `\d+` will report not report that 
it has oids, but the oid column is still present and returns the same 
result before updating pg_class.



Just Dont!
So I'm definitely missing something. I really need a point in the 
right direction Please help! ;)



There are a few of methods to get rid of OIDs :

- ALTER TABLE .. SET WITHOUT OIDS (just mentioning, you already checked 
that)


- Use table copy +  use of a trigger to log changes : 
https://dba.stackexchange.com/questions/259359/eliminating-oids-while-upgrading-postgresql-from-9-4-to-12


- Use of Inheritance (the most neat solution I have seen, this is what I 
used for a 2TB table conversion) : 
https://www.percona.com/blog/performing-etl-using-inheritance-in-postgresql/




CG





--
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt

Re: Retrieve results in PostgreSQL stored procedure allowing query parallelism

[David G. Johnston]

On Tue, Nov 21, 2023 at 11:31 AM Joan Pujol  wrote:

> If I try to do something like:
> EXECUTE SELECT INTO ARRAY_AGG(t.*) INTO result_records
> Would internally use cursors too and have the same restrictions?
>
>
Producing a single row single column result and then storing it into a
variable should leverage parallelism if the query is amenable to it.

David J.

Removing oids with pg_repack

[CG]

I have a very large PostgreSQL 9.5 database that still has very large tables 
with oids. I'm trying to get rid of the oids with as little downtime as 
possible so I can prep the database for upgrade past PostgreSQL 11. I had a 
wild idea to mod pg_repack to write a new table without oids. I think it almost 
works. 
To test out my idea I made a new table wipe_oid_test with oids. I filled it 
with a few rows of data.
Next I make this modification:

CREATE OR REPLACE FUNCTION repack.get_storage_param(oid)  RETURNS TEXT 
AS$$SELECT string_agg(param, ', ')FROM (    -- table storage parameter    
SELECT unnest(reloptions) as param    FROM pg_class    WHERE oid = $1    UNION 
ALL    -- TOAST table storage parameter    SELECT ('toast.' || 
unnest(reloptions)) as param    FROM (        SELECT reltoastrelid from 
pg_class where oid = $1         ) as t,        pg_class as c    WHERE c.oid = 
t.reltoastrelid    UNION ALL    /*-- table oid    SELECT 'oids = ' ||        
CASE WHEN relhasoids            THEN 'true'            ELSE 'false'        END  
  FROM pg_class    WHERE oid = $1*/
 SELECT 'oids = false' --my modification
     ) as t$$LANGUAGE sql STABLE STRICT;

Then I run pg_repack:
$ pg_repack -d mydata -Upostgres -t wipe_oid_test -eLOG: (query) SET 
search_path TO pg_catalog, pg_temp, publicLOG: (query) SET search_path TO 
pg_catalog, pg_temp, publicLOG: (query) select repack.version(), 
repack.version_sql()LOG: (query) SET statement_timeout = 0LOG: (query) SET 
search_path = pg_catalog, pg_temp, publicLOG: (query) SET client_min_messages = 
warningLOG: (query) SELECT t.*, coalesce(v.tablespace, t.tablespace_orig) as 
tablespace_dest FROM repack.tables t,  (VALUES (quote_ident($1::text))) as v 
(tablespace) WHERE (relid = $2::regclass) ORDER BY t.relname, t.schemanameLOG:  
(param:0) = (null)LOG:  (param:1) = wipe_oid_testINFO: repacking table 
"public.wipe_oid_test"LOG: (query) SELECT pg_try_advisory_lock($1, 
CAST(-2147483648 + $2::bigint AS integer))LOG:  (param:0) = 16185446LOG:  
(param:1) = 2273648077LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTEDLOG: 
(query) SET LOCAL statement_timeout = 100LOG: (query) LOCK TABLE 
public.wipe_oid_test IN ACCESS EXCLUSIVE MODELOG: (query) RESET 
statement_timeoutLOG: (query) SELECT pg_get_indexdef(indexrelid) FROM pg_index 
WHERE indrelid = $1 AND NOT indisvalidLOG:  (param:0) = 2273648077LOG: (query) 
SELECT indexrelid, repack.repack_indexdef(indexrelid, indrelid, $2, FALSE)  
FROM pg_index WHERE indrelid = $1 AND indisvalidLOG:  (param:0) = 
2273648077LOG:  (param:1) = (null)LOG: (query) SELECT 
repack.conflicted_triggers($1)LOG:  (param:0) = 2273648077LOG: (query) CREATE 
TYPE repack.pk_2273648077 AS (k text)LOG: (query) CREATE TABLE 
repack.log_2273648077 (id bigserial PRIMARY KEY, pk repack.pk_2273648077, row 
public.wipe_oid_test)LOG: (query) CREATE TRIGGER repack_trigger AFTER INSERT OR 
DELETE OR UPDATE ON public.wipe_oid_test FOR EACH ROW EXECUTE PROCEDURE 
repack.repack_trigger('INSERT INTO repack.log_2273648077(pk, row) VALUES( CASE 
WHEN $1 IS NULL THEN NULL ELSE (ROW($1.k)::repack.pk_2273648077) END, $2)')LOG: 
(query) ALTER TABLE public.wipe_oid_test ENABLE ALWAYS TRIGGER 
repack_triggerLOG: (query) SELECT 
repack.disable_autovacuum('repack.log_2273648077')LOG: (query) BEGIN ISOLATION 
LEVEL READ COMMITTEDLOG: (query) SELECT pg_backend_pid()LOG: (query) SELECT pid 
FROM pg_locks WHERE locktype = 'relation' AND granted = false AND relation = 
2273648077 AND mode = 'AccessExclusiveLock' AND pid <> pg_backend_pid()LOG: 
(query) COMMITLOG: (query) BEGIN ISOLATION LEVEL SERIALIZABLELOG: (query) 
SELECT set_config('work_mem', current_setting('maintenance_work_mem'), 
true)LOG: (query) SELECT coalesce(array_agg(l.virtualtransaction), '{}')   FROM 
pg_locks AS l   LEFT JOIN pg_stat_activity AS a     ON l.pid = a.pid   LEFT 
JOIN pg_database AS d     ON a.datid = d.oid   WHERE l.locktype = 'virtualxid'  
 AND l.pid NOT IN (pg_backend_pid(), $1)   AND (l.virtualxid, 
l.virtualtransaction) <> ('1/1', '-1/0')   AND (a.application_name IS NULL OR 
a.application_name <> $2)  AND a.query !~* E'^\\s*vacuum\\s+'   AND a.query !~ 
E'^autovacuum: '   AND ((d.datname IS NULL OR d.datname = current_database()) 
OR l.database = 0)LOG:  (param:0) = 15246LOG:  (param:1) = pg_repackLOG: 
(query) DELETE FROM repack.log_2273648077LOG: (query) SELECT pid FROM pg_locks 
WHERE locktype = 'relation' AND granted = false AND relation = 2273648077 AND 
mode = 'AccessExclusiveLock' AND pid <> pg_backend_pid()LOG: (query) SET LOCAL 
statement_timeout = 100LOG: (query) LOCK TABLE public.wipe_oid_test IN ACCESS 
SHARE MODELOG: (query) RESET statement_timeoutLOG: (query) CREATE TABLE 
repack.table_2273648077 WITH (oids = false) TABLESPACE pg_default AS SELECT k,v 
FROM ONLY public.wipe_oid_test WITH NO DATALOG: (query) INSERT INTO 
repack.table_2273648077 SELECT k,v FROM ONLY public.wipe_oid_testLOG: (query) 
SELECT repack.disable_autovacuum('repack.table_2273648077')LOG: (query) 
COMMITLOG: 

Re: Retrieve results in PostgreSQL stored procedure allowing query parallelism

[Joan Pujol]

Thanks, David.

If I try to do something like:
EXECUTE SELECT INTO ARRAY_AGG(t.*) INTO result_records
Would internally use cursors too and have the same restrictions?

Cheers,

On Tue, 21 Nov 2023 at 19:22, David G. Johnston
 wrote:
>
> On Tue, Nov 21, 2023, 11:10 Joan Pujol  wrote:
>>
>> I want to execute an SQL query and process its results inside a stored
>> procedure without preventing query parallelism. Since I don't want to
>> prevent query parallelism, cursors can't be used, and I would like to
>> avoid creating a temporal table.
>>
>> Is this possible? If so, what is the best way to execute the query,
>> retrieve all results in memory, and process them inside the stored
>> procedure?
>
>
> You must use create table as if you want a result that is both accessible to 
> subsequent statements and uses parallelism to be produced.  There is no 
> saving results into memory - you either save them explicitly or iterate over 
> them and the later prevents parallelism as you've noted.
>
> David J.



-- 
Joan Jesús Pujol Espinar
http://www.joanpujol.cat

Re: Retrieve results in PostgreSQL stored procedure allowing query parallelism

[David G. Johnston]

On Tue, Nov 21, 2023, 11:10 Joan Pujol  wrote:

> I want to execute an SQL query and process its results inside a stored
> procedure without preventing query parallelism. Since I don't want to
> prevent query parallelism, cursors can't be used, and I would like to
> avoid creating a temporal table.
>
> Is this possible? If so, what is the best way to execute the query,
> retrieve all results in memory, and process them inside the stored
> procedure?
>

You must use create table as if you want a result that is both accessible
to subsequent statements and uses parallelism to be produced.  There is no
saving results into memory - you either save them explicitly or iterate
over them and the later prevents parallelism as you've noted.

David J.

>

Retrieve results in PostgreSQL stored procedure allowing query parallelism

[Joan Pujol]

I want to execute an SQL query and process its results inside a stored
procedure without preventing query parallelism. Since I don't want to
prevent query parallelism, cursors can't be used, and I would like to
avoid creating a temporal table.

Is this possible? If so, what is the best way to execute the query,
retrieve all results in memory, and process them inside the stored
procedure?

-- 
Joan Pujol

Re: client/server versions

[Adrian Klaver]

On 11/21/23 03:28, Dick Visser wrote:

Hi

I'm working with AWS RDS PG instances that have been created over time, 
and that by now are a mix of several major/minor versions ranging from 
12 to 15.
The initial configuration and management is done from an EC2 instance 
running Debian 11, which has Postrgres 13.13.
I already found out that there are compatibility issues if I use the 
client programs from the Debian VM when they're older than the server 
version.
To avoid this I tried to run the client programs from a dedicated PG 
container image with the same version as the server.

So instead of running:

PGSERVICE=foobar psql

I would now run:

podman run -e PGSERVICE=foobar -v 
$(pwd)/.pg_service.conf:/root/.pg_service.conf --rm -it 
docker.io/library/postgres:15.3  
psql


This is reasonably straightforward and seems to just work.
Now that I have a way to pick whatever client version I need, I wonder 
what the best strategy is wrt versions...
Keeping them exactly the same, or is it better to always use the latest 
client version?


Not sure how you installed the Postgres versions and what the OS'es are 
for all the instances? If you used the Debian/Ubuntu repos or Postgres 
community repo Debian/Ubuntu packages then you got the program 
pg_wrapper installed.


Per man pg_wrapper or postgresql-common(they point to the same thing) file:

"""
 This program is run only as a link to names which correspond to 
PostgreSQL programs in /usr/lib/postgresql/version/bin. It determines 
the configured cluster and database for the user and calls the 
appropriate version of the desired program to connect to that cluster 
and database, supplying any specified options to thatcommand.


...


For psql, pg_archivecleanup, and pg_isready, pg_wrapper will always use 
the binary from the newest PostgreSQL version installed, as these are 
downwards compatible.

"""

I know that the latter is a bit easier as one can just pick an untagged 
container image, because that corresponds to the latest version.
Also I'm thinking that using the latest version of pg_dump/pg_restore 
allows you to have more options.


thx

Dick Visser



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

Re: client/server versions

[Dick Visser]

On Tue, 21 Nov 2023 at 15:00, Thiemo Kellner 
wrote:

>
> Ok, fair enough, but it puzzles me that the effort shall be put into
> making a multitude of client verions working flawlessly with a multitude
> of server versions instead of holding server (and client) versions the
> same.
>

Yes, I know, but the method I described is a rather trivial way to make
things work in this situation.
My own deduction is that using latest version clients is probably better
than using same version clients.

Keep in mind that there are situations where users of clients programs
aren't in control of server bits, so this answer would be helpful for them
as well.

Dick

Re: client/server versions

[Ron Johnson]

On Tue, Nov 21, 2023 at 8:31 AM Dick Visser  wrote:

> On Tue, 21 Nov 2023 at 14:07, Ron Johnson  wrote:
>
>> On Tue, Nov 21, 2023 at 6:29 AM Dick Visser  wrote:
>>
>>> Hi
>>>
>>> I'm working with AWS RDS PG instances that have been created over time,
>>> and that by now are a mix of several major/minor versions ranging from 12
>>> to 15.
>>> The initial configuration and management is done from an EC2 instance
>>> running Debian 11, which has Postrgres 13.13.
>>> I already found out that there are compatibility issues if I use the
>>> client programs from the Debian VM when they're older than the server
>>> version.
>>>
>>
>> Like what?
>>
>
> For starters:
>
> pg_restore: error: unsupported version (1.15) in file header
>
>
Upgrade Postgresql on the Debian 11 EC2 instance from 13.11 to 15.latest.

Or install Postgresql 12, 14 and 15 alongside Pg 13.  They all go in
separate directories.

Re: client/server versions

[Daniel Gustafsson]

> On 21 Nov 2023, at 14:31, Dick Visser  wrote:
> On Tue, 21 Nov 2023 at 14:07, Ron Johnson  > wrote:
> On Tue, Nov 21, 2023 at 6:29 AM Dick Visser  > wrote:

> I already found out that there are compatibility issues if I use the client 
> programs from the Debian VM when they're older than the server version.
> 
> Like what?
> 
> For starters:
> 
> pg_restore: error: unsupported version (1.15) in file header

Older versions of tools are not guaranteed to be compatible with newer versions
of the server.  You should use the dump/restore tools of the version you are
targeting.  For other clients like psql it's usually fine to use the latest
version against older servers.

If you have a mix of server versions installed you need to put in the work of
ensuring you are using the right version of the clients as well.

--
Daniel Gustafsson

Re: client/server versions

[Thiemo Kellner]

Am 21.11.2023 um 13:18 schrieb Dick Visser:


It is possible, it's just that there is no real need to at the moment, 
ao we have not spent that much effort on the topic.

At any time there will always be newer upstream versions.


Ok, fair enough, but it puzzles me that the effort shall be put into 
making a multitude of client verions working flawlessly with a multitude 
of server versions instead of holding server (and client) versions the same.

Re: client/server versions

[Dick Visser]

On Tue, 21 Nov 2023 at 14:07, Ron Johnson  wrote:

> On Tue, Nov 21, 2023 at 6:29 AM Dick Visser  wrote:
>
>> Hi
>>
>> I'm working with AWS RDS PG instances that have been created over time,
>> and that by now are a mix of several major/minor versions ranging from 12
>> to 15.
>> The initial configuration and management is done from an EC2 instance
>> running Debian 11, which has Postrgres 13.13.
>> I already found out that there are compatibility issues if I use the
>> client programs from the Debian VM when they're older than the server
>> version.
>>
>
> Like what?
>

For starters:

pg_restore: error: unsupported version (1.15) in file header

Re: client/server versions

[Ron Johnson]

On Tue, Nov 21, 2023 at 6:29 AM Dick Visser  wrote:

> Hi
>
> I'm working with AWS RDS PG instances that have been created over time,
> and that by now are a mix of several major/minor versions ranging from 12
> to 15.
> The initial configuration and management is done from an EC2 instance
> running Debian 11, which has Postrgres 13.13.
> I already found out that there are compatibility issues if I use the
> client programs from the Debian VM when they're older than the server
> version.
>

Like what?

psql can *sometimes* be wonky when running against newer servers, as can
PgAdmin4, but that's because they won't understand changed catalog tables.
Do the other client programs use new SQL features?

Re: pgBackRest on old installation

[Ray O'Donnell]

On 21/11/2023 11:59, Achilleas Mantzios - cloud wrote:

On 11/21/23 08:36, KK CHN wrote:

Thank you.  Its worked out well. But a basic doubt ? is storing the DB 
superuser password in .pgpass is advisable ? What other options do we 
have ?


The .pgpass file is stored in the user's home directory, and from memory 
I think it won't get used at all if the permissions allow anyone other 
than the user to read it, so it's safer than it sounds!


Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: client/server versions

[Dick Visser]

On Tue, 21 Nov 2023 at 12:40, Thiemo Kellner 
wrote:

> Hi Dick
>
> Out of curiosity, what is the reason there is this zoo of versions. Is
> it impossible to align them to one



It is possible, it's just that there is no real need to at the moment, ao
we have not spent that much effort on the topic.
At any time there will always be newer upstream versions.

>

Re: pgBackRest on old installation

[Achilleas Mantzios - cloud]

On 11/21/23 08:36, KK CHN wrote:

Thank you.  Its worked out well. But a basic doubt ? is storing the DB 
superuser password in .pgpass is advisable ? What other options do we 
have ?

#su postgres
bash-4.2$ cd

bash-4.2$ cat .pgpass
*:*:*:postgres:your_password
bash-4.2$


root has access to any file, but if you give password on the command 
line or env variable then you give this info to all users in the system.





On Mon, Nov 20, 2023 at 4:16 PM Achilleas Mantzios - cloud 
 wrote:



On 11/20/23 12:31, KK CHN wrote:

list,

I am trying pgBackRest on an RHEL 7.6 and old EDB 10 database
cluster( a legacy application.)

I have installed pgbackrest through  package install on RHEL7.6
But unable to get the basic stanza-creation working It throws an
error.


* /etc/pgbackrest.conf  as follows..*

[demo]
pg1-path=/app/edb/as10/data
pg1-port = 5444
pg1-socket-path=/tmp

[global]

repo1-cipher-pass=sUAeceWoDffSz9Q/d8sWREHe+wte3uOO9lggn5/5mTkQEempvBxQk5UbxsrDzHbw

repo1-cipher-type=aes-256-cbc
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
backup-user=postgres


[global:archive-push]
compress-level=3
#



[root@dbs ~]# pgbackrest version
pgBackRest 2.48
[root@dbs ~]#
#

*Postgres conf as follows... *

listen_addresses = '*'
port = 5444
unix_socket_directories = '/tmp'

archive_command = 'pgbackrest --stanza=demo archive-push %p'
archive_mode = on
log_filename = 'postgresql.log'
max_wal_senders = 3
wal_level = replica

#


*ERROR  Getting as follows ..    What went wrong here ??*


 [root@dbs ~]# sudo -u postgres pgbackrest
--stanza=demo --log-level-console=info stanza-create
2023-11-20 21:04:05.223 P00   INFO: stanza-create command begin
2.48: --exec-id=29527-bf5e2f80 --log-level-console=info
--pg1-path=/app/edb/as10/data --pg1-port=5444
--pg1-socket-path=/tmp --repo1-cipher-pass=
--repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest
--stanza=demo
WARN: unable to check pg1: [DbConnectError] unable to connect to
'dbname='postgres' port=5444 host='/tmp'': connection to server
on socket "/tmp/.s.PGSQL.5444" failed: fe_sendauth: no password
supplied
ERROR: [056]: unable to find primary cluster - cannot proceed
       HINT: are all available clusters in recovery?
2023-11-20 21:04:05.224 P00   INFO: stanza-create command end:
aborted with exception [056]
[root@dbs ~]#

It complains about the password.  I followed the below tutorial
link, but no mention of password (Where to supply password, what
parameter where ?) setting here ==>
https://pgbackrest.org/user-guide-rhel.html


This is about the user connecting to the db, in general,
pgbackrest has to connect like any other app/user. So, change your
.pgpass to contain smth like the below on the top of the file :

/tmp:5444:*:postgres:your_whatever_pgsql_password

and retry




Any hints welcome..  What am I missing here ??

Best,
Krishane

Re: client/server versions

[Thiemo Kellner]

Hi Dick

Out of curiosity, what is the reason there is this zoo of versions. Is 
it impossible to align them to one version?


Cheers

Thiemo

client/server versions

[Dick Visser]

Hi

I'm working with AWS RDS PG instances that have been created over time, and
that by now are a mix of several major/minor versions ranging from 12 to 15.
The initial configuration and management is done from an EC2 instance
running Debian 11, which has Postrgres 13.13.
I already found out that there are compatibility issues if I use the client
programs from the Debian VM when they're older than the server version.
To avoid this I tried to run the client programs from a dedicated PG
container image with the same version as the server.
So instead of running:

PGSERVICE=foobar psql

I would now run:

podman run -e PGSERVICE=foobar -v
$(pwd)/.pg_service.conf:/root/.pg_service.conf --rm -it
docker.io/library/postgres:15.3 psql

This is reasonably straightforward and seems to just work.
Now that I have a way to pick whatever client version I need, I wonder what
the best strategy is wrt versions...
Keeping them exactly the same, or is it better to always use the latest
client version?
I know that the latter is a bit easier as one can just pick an untagged
container image, because that corresponds to the latest version.
Also I'm thinking that using the latest version of pg_dump/pg_restore
allows you to have more options.

thx

Dick Visser