Re: initdb when data/ folder has mount points

[Ron Johnson]

On 02/21/2018 06:01 PM, Tom Lane wrote:

Ron Johnson <ron.l.john...@cox.net> writes:

Apparently, initdb assumes that data/ is one big mount point. However, we
have four mount points:
/var/lib/pgsql/9.6/data/backup
/var/lib/pgsql/9.6/data/base
/var/lib/pgsql/9.6/data/pg_log
/var/lib/pgsql/9.6/data/pg_xlog

Don't do that.

There's no reason for backup storage to be under the data directory (and
lots of good reasons for it not to be).  Just put it somewhere else.

The supported way to put pg_xlog on a separate volume is to put that
mount point somewhere else, and make $PGDATA/pg_xlog be a symlink to
it.  IIRC, there's an initdb option to help with that, though you can
also make it so manually after initdb.

For pg_log, just put it somewhere else and set the appropriate
configuration option to say where to write the postmaster log files.
Or you could use a symlink, like the solution for pg_xlog, but
I don't see any advantage there.

I don't see any point in making base/ be its own mount point.  Once
you get rid of those other subdirectories there's not going to be
enough "global" storage left to justify its own volume.


I'm replicating the structure in our existing systems.  Is there an (up to 
date) Best Practices document for these kinds of issues?



--
Money Angular momentum makes the world go 'round.

initdb when data/ folder has mount points

[Ron Johnson]

Hi,

v9.6.6

Apparently, initdb assumes that data/ is one big mount point. However, we 
have four mount points:

/var/lib/pgsql/9.6/data/backup
/var/lib/pgsql/9.6/data/base
/var/lib/pgsql/9.6/data/pg_log
/var/lib/pgsql/9.6/data/pg_xlog

They are all empty.  How do I convince it to ignore the fact that the 
directories exist?


Thanks

--
Money Angular momentum makes the world go 'round.

Re: pg_update to a new machine?

[Ron Johnson]

On 02/24/2018 08:18 AM, Stephen Frost wrote:

Greetings,

* Ron Johnson (ron.l.john...@cox.net) wrote:

The 2.8TB database must be moved to a new server in a new Data Center, and
upgraded from 8.4.17 to 9.6.6

Will this work?

pg_upgrade
 --old-datadir "CURSERVER://var/lib/pgsql/data"
 --new-datadir "NEWSERVER://var/lib/pgsql/data"
 --old-bindir "CURSERVER://usr/bin"
 --new-bindir "NEWSERVER://usr/bin"

No, you can't specify hostnames to pg_upgrade.


I didn't think it would work, but it was worth an ask...


You could possibly use
NFS or something else to be able to facilitate the above.


Or must I:
1. temporarily allocate 3TB of scratch space on the new server,

If you use --link mode during pg_upgrade, you don't actually need to
allocate that scratch space on the new server.


I'll look into that.


2. install 8.4 on the new server,
3. install 9.6.6 on the new server,

For pg_upgrade, you do need both versions installed on the server you're
running pg_upgrade on, yes.  Please be sure to use the latest minor
version of each major version if you go that route.


2. rsync CURSERVER://var/lib/pgsql/data to
NEWSERVER://var/lib/pgsql/8.4/data, and then

You can only perform this rsync with the database shut down, just to be
clear.  If you wanted to pull the data across with the database online,
you'd need to set up an archive_command and use a tool which works with
8.4 to perform an online backup (such as pgBackRest).


To set up log shipping on 8.4, I do this, which works well:

select pg_start_backup('some_meaningful_tag');
nohup rsync -avz /var/lib/pgsql/data/* postgres@${DESTIP}:/var/lib/pgsql/data/ &
select pg_stop_backup();

Would I, essentially (or in fact), have to set up log shipping from old to new?


3. pg_upgrade?

If you perform an online backup and then capture all of the WAL using
archive_command, you could stand up a warm standby with 8.4 on the new
server which is replaying the WAL as it's generated on the primary by
specifying a restore_command on the new server.


I guess that means "yes, set up log shipping"?


   Doing this, combined
with using pg_upgrade in --link mode, you would be able to perform the
flip from the old-server-on-8.4 to the new-server-with-9.6 in a
relatively short period of time (on the order of minutes-to-an-hour,
potentially).




Are there better ways?  (The pipe from current DC to new DC will be 10Gbps.)

The above approach would work, but you wouldn't be able to enable
checksums on the new server, which is something I'd certainly recommend
doing if you're able to.


We'll benchmark it.


   To get page-level checksums, you would need to
make sure you initdb the new server with them and then use the newer
pg_dump version to dump the 8.4 data out and then into the 9.6 server.
This could possibly be done as a pipe, but I'd probably find 1TB of
space somewhere and use parallel pg_dump


Is parallel pg_dump available on 8.4, or am I misinterpreting you?


  to extract the data out and
into a compressed logical dump and then parallel pg_restore to pull it
into the new server.  This would also re-check all constraints in the
system and rebuild all indexes, but would naturally require more
downtime.

Thanks!

Stephen



--
Angular momentum makes the world go 'round.

pg_update to a new machine?

[Ron Johnson]

The 2.8TB database must be moved to a new server in a new Data Center, and 
upgraded from 8.4.17 to 9.6.6


Will this work?

pg_upgrade
--old-datadir "CURSERVER://var/lib/pgsql/data"
--new-datadir "NEWSERVER://var/lib/pgsql/data"
--old-bindir "CURSERVER://usr/bin"
--new-bindir "NEWSERVER://usr/bin"

Or must I:
1. temporarily allocate 3TB of scratch space on the new server,
2. install 8.4 on the new server,
3. install 9.6.6 on the new server,
2. rsync CURSERVER://var/lib/pgsql/data to 
NEWSERVER://var/lib/pgsql/8.4/data, and then

3. pg_upgrade?

Are there better ways?  (The pipe from current DC to new DC will be 10Gbps.)

Thanks

--
Angular momentum makes the world go 'round.

Re: pg_update to a new machine?

[Ron Johnson]

On 02/24/2018 03:10 PM, Stephen Frost wrote:
[snip]

To set up log shipping on 8.4, I do this, which works well:

select pg_start_backup('some_meaningful_tag');
nohup rsync -avz /var/lib/pgsql/data/* postgres@${DESTIP}:/var/lib/pgsql/data/ &
select pg_stop_backup();

That's not log shipping, for log shipping you need to specify an
archive_command and actually capture all of the WAL generated, or, at a
minimum, the WAL generated between the start and stop backup calls.


That's steps #8, 9 and 10 (in our checklist) of setting up log shipping.  
Step #4 is configuring the archive_* statements in postgresql.conf.




--
Angular momentum makes the world go 'round.

Re: pg_update to a new machine?

[Ron Johnson]

On 02/24/2018 06:40 PM, Stephen Frost wrote:

Ron,

* Ron Johnson (ron.l.john...@cox.net) wrote:

On 02/24/2018 03:10 PM, Stephen Frost wrote:
[snip]

To set up log shipping on 8.4, I do this, which works well:

select pg_start_backup('some_meaningful_tag');
nohup rsync -avz /var/lib/pgsql/data/* postgres@${DESTIP}:/var/lib/pgsql/data/ &
select pg_stop_backup();

That's not log shipping, for log shipping you need to specify an
archive_command and actually capture all of the WAL generated, or, at a
minimum, the WAL generated between the start and stop backup calls.

That's steps #8, 9 and 10 (in our checklist) of setting up log shipping.
Step #4 is configuring the archive_* statements in postgresql.conf.

I would still recommend you use a tool developed specifically for taking
proper PG backups which validates that all WAL generated during a backup
is properly archived, such as pgBackRest, which also makes sure to sync
all the data out to persistent storage and can also operate in parallel
and perform incremental restores.


OK.  I'll try to get it installed.  (Lots of paperwork, etc.)


--
Angular momentum makes the world go 'round.

Installing 9.6.6 to a RHEL 6.7 server with no Internet access

[Ron Johnson]

Hi.

According to https://www.postgresql.org/download/linux/redhat/ I must first 
install the repository.  However, since that system doesn't have Internet 
access, I need to manually copy the files from my Windows laptop to the RHEL 
6.7 server and then localinstall them.


So, the question: what packages do I need to download from 
https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6.7-x86_64/ in 
order to "yum localinstall postgresql96" and "yum localinstall 
postgresql96-server"?


Thanks

--
Money Angular momentum makes the world go 'round.

Re: Installing 9.6.6 to a RHEL 6.7 server with no Internet access

[Ron Johnson]

It does.  Thank you.

On 02/21/2018 10:03 AM, Alvaro Aguayo Garcia-Rada wrote:

Not sure if this may help you. A few months ago, I made an automated OpenSUSE 
installer which also had to install PostgreSQL 9.6 server using the RHEL PGDG 
releases. There's no YUM there, so I did it instead with RPM files directly.

The RPM files I included on my autoinstall ISO are the following:

postgresql96-9.6.2-2PGDG.rhel7.x86_64.rpm
postgresql96-contrib-9.6.2-2PGDG.rhel7.x86_64.rpm
postgresql96-devel-9.6.2-2PGDG.rhel7.x86_64.rpm
postgresql96-libs-9.6.2-2PGDG.rhel7.x86_64.rpm
postgresql96-server-9.6.2-2PGDG.rhel7.x86_64.rpm

Of course, you may want or need to use more recent versions. Also, you may ommit 
the contrib & devel packages, as they may not always be needed(in my case, they 
were)

Regarding dependencies, with a quick look, I can say these will require a few 
common packages:

- zlib
- pthreads
- readline
- openssl
- libxml2
- libxslt
- libuuid
- krb5
- libldap / openldap

That seems to be all required packages. Hope this helps.

Regards,

Alvaro Aguayo
Operations Manager
Open Comb Systems E.I.R.L.

Office: (+51-1) 3377813 | Mobile: (+51) 995540103 | (+51) 954183248
Web: www.ocs.pe

- Original Message -
From: "Ron Johnson" <ron.l.john...@cox.net>
To: "PostgreSql-general" <pgsql-gene...@postgresql.org>
Sent: Wednesday, 21 February, 2018 10:49:00
Subject: Installing 9.6.6 to a RHEL 6.7 server with no Internet access

Hi.

According to https://www.postgresql.org/download/linux/redhat/ I must first
install the repository.  However, since that system doesn't have Internet
access, I need to manually copy the files from my Windows laptop to the RHEL
6.7 server and then localinstall them.

So, the question: what packages do I need to download from
https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6.7-x86_64/ in
order to "yum localinstall postgresql96" and "yum localinstall
postgresql96-server"?

Thanks




--
Money Angular momentum makes the world go 'round.

Re: initdb when data/ folder has mount points

[Ron Johnson]

On 02/22/2018 07:22 AM, David Steele wrote:

On 2/22/18 1:16 AM, Michael Paquier wrote:

On Wed, Feb 21, 2018 at 07:56:38PM -0500, David Steele wrote:

On 2/21/18 7:01 PM, Tom Lane wrote:

For pg_log, just put it somewhere else and set the appropriate
configuration option to say where to write the postmaster log files.
Or you could use a symlink, like the solution for pg_xlog, but
I don't see any advantage there.

Symlinking pg_log is not ideal because the logs end up in the backup. It
gets pretty weird when those logs get restored to a standby and somebody
starts reading them.

log_directory in postgresql.conf san be set up with an absolute
directory value.  So there is no actual need for a symlink with pg_log.
This also reduces the amount of data transfered as part of base
backups without actually needing them.

Yes, I really should have said having pg_log in the data directory at
all is not ideal, symlinked or no.


Is there a Best Practices wiki? 
(https://wiki.postgresql.org/wiki/Detailed_installation_guides is pretty old 
and thin.)



--
Money Angular momentum makes the world go 'round.

psql in a bash function

[Ron Johnson]

Hi,

Because I need to log into many servers, I created functions as keyboard 
shortcuts (not aliases, since I will want to embed these shortcuts in other 
functions).


psqlxyz ()
{
    echo "P1=$1";
    echo "P2=$2";
    psql -U postgres -h XYZ $@
}

This is the (simple, test) command that I want to run, which works when run 
explicitly using psql, but not my function.  Any ideas why the function 
isn't properly passing the "-c" and '"select ..."' to psql?


$ psql -U postgres -h XYZ -c "select oid, datname from pg_database;"
   oid   |    datname
-+
   1 | template1
   11563 | template0
   11564 | postgres
   16404 | test1
 3039800 | ABCD
  319011 | EFGH
  649861 | IJKL
(7 rows)

$ psqldba -c *'*"select oid, datname from pg_database;"*'*
P1=*-c*
P2=*"select oid, datname from pg_database;"*
psql: warning: extra command-line argument "datname" ignored
psql: warning: extra command-line argument "from" ignored
psql: warning: extra command-line argument "pg_database;"" ignored
psql: FATAL:  database "oid," does not exist

Thanks

--
Angular momentum makes the world go 'round.

Version upgrade: is restoring the postgres database needed?

[Ron Johnson]

Or do we just apply the globals.sql created by "pg_dumpall --globals-only"?

(We're upgrading by restoring all databases on a new server, that, 
naturally, has it's own new postgres, template0 and template1 databases.)



Thanks

--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

On 03/01/2018 02:20 AM, Alban Hertroys wrote:
[snip]

Not to mention that not all types of tables necessarily have suitable 
candidates for a primary key. You could add a surrogate key based on a serial 
type, but in such cases that may not serve any purpose other than to have some 
arbitrary primary key.

An example of such tables is a monetary transaction table that contains records 
for deposits and withdrawals to accounts. It will have lots of foreign key 
references to other tables, but rows containing the same values are probably 
not duplicates.
Adding a surrogate key to such a table just adds overhead, although that could 
be useful in case specific rows need updating or deleting without also 
modifying the other rows with that same data - normally, only insertions and 
selections happen on such tables though, and updates or deletes are absolutely 
forbidden - corrections happen by inserting rows with an opposite transaction.


Wouldn't the natural pk of such a table be timestamp+seqno, just as the 
natural pk of a transaction_detail table be transaction_no+seqno?


--
Angular momentum makes the world go 'round.

Re: Version upgrade: is restoring the postgres database needed?

[Ron Johnson]

On 03/01/2018 08:46 AM, Melvin Davidson wrote:



On Thu, Mar 1, 2018 at 5:24 AM, Ron Johnson <ron.l.john...@cox.net 
<mailto:ron.l.john...@cox.net>> wrote:



Or do we just apply the globals.sql created by "pg_dumpall
--globals-only"?

(We're upgrading by restoring all databases on a new server, that,
naturally, has it's own new postgres, template0 and template1 databases.)


>is restoring the postgres database needed?

That would depend on how you did the dump. If you did a_complete 
pg_dumpall (did not use -g or any other limiting flags), then all roles 
and databases are contained
in the output file created. NOTE: restoring from the dumped file will 
require rebuilding all indexes, because indexes are not dumped.


No, I do:

$ pg_dump -Fc PROD > PROD.pgdump
$ pg_dump --globals-only postgres > globals.sql
$ pg_dump -Fc postgres > postgres.pgdump



--
Angular momentum makes the world go 'round.

Re: Reindex doesn't eliminate bloat

[Ron Johnson]

On 03/12/2018 10:48 PM, Nikolay Samokhvalov wrote:
On Tue, Mar 13, 2018 at 1:28 AM, Ron Johnson <ron.l.john...@cox.net 
<mailto:ron.l.john...@cox.net>> wrote:


On 03/12/2018 05:20 PM, Nikolay Samokhvalov wrote:

On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson <ron.l.john...@cox.net
<mailto:ron.l.john...@cox.net>> wrote:

v8.4.12

This is *very* old version, not supported by the community for many
years. Check https://www.postgresql.org/ to seecurrentlysupported
versions.
You need to upgrade it.


Don't even think I'm in control of when -- or even if -- the customer
decides to upgrade.

That being the case, do you have an answer to the question?


Those queries from wiki for table and index bloat estimation are for 
estimation only. In many cases they show very wrong results. Better (yet 
not ideal) approach is using pgstattuple extension (though I'm not sure it 
existed back in 2009).


Can you provide table and index definition and, if you can, some sample data?


Sadly, no sample data.  (It's all PCI controlled.)

Index idx_item_mapping_rp7_y2016m03itemmapping_custom_userfield_801 has 40% 
bloat.


Thanks.

--
Angular momentum makes the world go 'round.
TAPd=# \d tms.item_mapping_rp7_y2013m03
   Table 
"tms.item_mapping_rp7_y2013m03"
 Column |Type | 
Modifiers  
+-+
 item_mapping_id| integer | not null default 
nextval('tms.item_mapping_item_mapping_id_seq'::regclass)
 item_seq_no| numeric(10,0)   | 
 account_no | character varying(255)  | 
 amount | numeric(21,2)   | 
 gross_amount   | numeric(21,2)   | 
 net_amount | numeric(21,2)   | 
 amount_3   | numeric(21,2)   | 
 custom_userfield_401   | character varying(255)  | 
 custom_userfield_402   | character varying(255)  | 
 custom_userfield_403   | character varying(255)  | 
 custom_userfield_404   | character varying(255)  | 
 custom_userfield_405   | character varying(255)  | 
 custom_userfield_416   | character varying(16)   | 
 custom_userfield_417   | character varying(16)   | 
 custom_userfield_418   | character varying(16)   | 
 custom_userfield_419   | character varying(16)   | 
 custom_userfield_420   | numeric(10,0)   | 
 custom_userfield_421   | numeric(10,0)   | 
 custom_userfield_422   | numeric(10,0)   | 
 custom_userfield_423   | numeric(21,2)   | 
 custom_userfield_424   | numeric(21,2)   | 
 custom_userfield_425   | numeric(21,2)   | 
 custom_userfield_426   | numeric(10,0)   | 
 custom_userfield_431   | character varying(16)   | 
 custom_userfield_432   | character varying(16)   | 
 custom_userfield_433   | character varying(16)   | 
 custom_userfield_434   | character varying(16)   | 
 custom_userfield_436   | character varying(16)   | 
 custom_userfield_437   | numeric(10,0)   | 
 custom_userfield_438   | numeric(10,0)   | 
 custom_userfield_439   | numeric(10,0)   | 
 custom_userfield_440   | numeric(10,0)   | 
 custom_userfield_441   | numeric(10,0)   | 
 custom_userfield_442   | numeric(10,0)   | 
 custom_userfield_443   | numeric(10,0)   | 
 custom_userfield_444   | character varying(1)| 
 custom_userfield_445   | character varying(1)| 
 custom_userfield_446   | character varying(1)| 
 custom_userfield_447   | character varying(1)| 
 custom_userfield_448   | character varying(1)| 
 custom_userfield_449   | character varying(1)| 
 custom_userfield_450   | character varying(1)| 
 custom_userfield_451   | character varying(1)| 
 custom_userfield_452   | character varying(1)| 
 custom_userfield_453   | numeric(21,2)   | 
 custom_userfield_454   | numeric(21,2)   | 
 custom_userfield_455   | numeric(21,2)   | 
 custom_userfield_456   | numeric(21,2)   | 
 custom_userfield_457   | numeric(21,2)   | 
 custom_userfield_458   | numeric(21,2)   | 
 custom_userfield_459   | numeric(21,2)   | 
 custom_userfield_460   | numeric(21,2)   | 
 cds_invoice_seq_number | numeric(10,0)   | 
 created_on | timestamp without time zone | not null
 created_by | integer | 
 modified_on| timestamp without time zone | 
 modified_by| integer | 
 ty

Re: Reindex doesn't eliminate bloat

[Ron Johnson]

On 03/12/2018 05:20 PM, Nikolay Samokhvalov wrote:
On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson <ron.l.john...@cox.net 
<mailto:ron.l.john...@cox.net>> wrote:


v8.4.12

This is *very* old version, not supported by the community for many years. 
Check https://www.postgresql.org/ to seecurrentlysupported versions.

You need to upgrade it.


Don't even think I'm in control of when -- or even if -- the customer 
decides to upgrade.


That being the case, do you have an answer to the question?


--
Angular momentum makes the world go 'round.

Reindex doesn't eliminate bloat

[Ron Johnson]

v8.4.12

According to this (https://pastebin.com/TJB32n5M) query, which I thought I 
got from https://wiki.postgresql.org/wiki/Index_Maintenance, a list of 
indexes and their bloat is generated.


After reindexing a table with a large amount of reported bloat (column 
bloat_pct says 29%), re-running the query shows no change in the amount of 
bloat.  This is a historical table, and VACUUM VERBOSE shows that there's 
nothing to free up.


Is this something that I must live with, or am I misinterpreting the query?

Thanks,

--
Angular momentum makes the world go 'round.

Re: Reindex doesn't eliminate bloat

[Ron Johnson]

On 03/12/2018 05:55 PM, Adrian Klaver wrote:

On 03/12/2018 03:05 PM, Ron Johnson wrote:

v8.4.12

According to this (https://pastebin.com/TJB32n5M) query, which I thought 
I got from https://wiki.postgresql.org/wiki/Index_Maintenance, a list of 
indexes and their bloat is generated.


After reindexing a table with a large amount of reported bloat (column 
bloat_pct says 29%), re-running the query shows no change in the amount 


First I am not seeing a column bloat_pct in the query you linked to, so 
are you sure that is the actual query you used?


Sorry.  bloat_pct is renamed bloat_ratio.



of bloat.  This is a historical table, and VACUUM VERBOSE shows that 
there's nothing to free up.


Is this something that I must live with, or am I misinterpreting the query?


Honestly I have not worked my way in depth through the query you show, 
though I did notice it uses pg_stats. What happens if run ANALYZE 
(https://www.postgresql.org/docs/8.4/static/sql-analyze.html) to update 
the stats?



I did ANALYZE VERBOSE on the underlying table.  No change.


--
Angular momentum makes the world go 'round.

Re: Ability to create tables

[Ron Johnson]

On 03/09/2018 05:46 PM, Tom Lane wrote:

Ron Johnson <ron.l.john...@cox.net> writes:

Even though I revoked the  CREATE priv on role ABCREADONLY, it's still able
to create tables.  What can I do to prevent this?
$ psql -c 'revoke create on database "ABC123" from "ABCREADONLY";'

That revokes the ability to create new schemas within that database
(which I suspect the role did not have anyway).  What you need is
to remove its ability to create objects within the public schema
within that database.  By default, that ability is granted to PUBLIC,
so that "revoke create on schema public from "ABCREADONLY";" won't
help either.  What you have to do is "revoke create on schema public
from public", and then grant it back to just the roles that should have
it.

If you don't want the role creating temp tables either, you need to
revoke its TEMP right on the database (which *is* a database-level
privilege).  Again, this'll involve disallowing that to PUBLIC,
since that default grant is how it's getting the privilege.


Thanks.


--
Angular momentum makes the world go 'round.

Re: pgpass hostname and IP address

[Ron Johnson]

On 03/09/2018 10:26 PM, David G. Johnston wrote:
On Friday, March 9, 2018, Ron Johnson <ron.l.john...@cox.net 
<mailto:ron.l.john...@cox.net>> wrote:


(8.4 and 9.2, but soon to 9.6)

If we sometimes access a db server by IP address, and sometimes by
hostname, must we have two entries for each server+user (one with the
hostname and the other with IP address), or is there a way to put them
both on the same lin


https://www.postgresql.org/docs/10/static/libpq-pgpass.html

So unless you can make a wildcard work you are stuck with two separate lines.


Yeah, I read that (well, the ones for my ancient versions), but hoped there 
might be something I missed, or some obscure and clever trick.


--
Angular momentum makes the world go 'round.

pgpass hostname and IP address

[Ron Johnson]

(8.4 and 9.2, but soon to 9.6)

If we sometimes access a db server by IP address, and sometimes by hostname, 
must we have two entries for each server+user (one with the hostname and the 
other with IP address), or is there a way to put them both on the same line?


--
Angular momentum makes the world go 'round.

Ability to create tables

[Ron Johnson]

Hi,

Archaic v9.2.7

Even though I revoked the  CREATE priv on role ABCREADONLY, it's still able 
to create tables.  What can I do to prevent this?


\c postgres
CREATE ROLE "ABCREADONLY" LOGIN INHERIT PASSWORD 'Flying.Fox';
GRANT CONNECT ON DATABASE "ABC123" TO "ABCREADONLY";

\c ABC123
GRANT USAGE ON SCHEMA public, ABC, tms TO "ABCREADONLY";
GRANT SELECT ON ALL TABLES IN SCHEMA public, ABC, tms TO "ABCREADONLY";
ALTER DEFAULT PRIVILEGES IN SCHEMA public, ABC, tms GRANT SELECT ON TABLES 
TO "ABCREADONLY";


$ psql -c 'revoke create on database "ABC123" from "ABCREADONLY";'
REVOKE
$ psql -d ABC123 -U ABCREADONLY -c "create table bar (f1 integer);"
CREATE TABLE
$ psql -d ABC123 -U ABCREADONLY -c "\d bar"
  Table "public.bar"
Column |  Type   | Modifiers
+-+---
f1 | integer |

Thanks

--
Angular momentum makes the world go 'round.

Re: Reindex doesn't eliminate bloat

[Ron Johnson]

On 03/13/2018 06:10 PM, Joe Conway wrote:

On 03/12/2018 09:16 PM, Ron Johnson wrote:

On 03/12/2018 10:48 PM, Nikolay Samokhvalov wrote:

Those queries from wiki for table and index bloat estimation are for
estimation only. In many cases they show very wrong results. Better
(yet not ideal) approach is using pgstattuple extension (though I'm
not sure it existed back in 2009).

Can you provide table and index definition and, if you can, some
sample data?

Sadly, no sample data.  (It's all PCI controlled.)

Index idx_item_mapping_rp7_y2016m03itemmapping_custom_userfield_801 has
40% bloat.

Assuming the data in the indexed column(s) is not highly correlated with
the physical table order (i.e. it is roughly random), about 50% density
is theoretically expected.


What does physical table order have to do with b-tree organization, 
especially in a freshly reindexed table using the default 90% fill factor?



  In fact, in some empirical testing, I have
seen a long term steady state value of closer to 44% if I remember
correctly (but perhaps that was related to the way I was testing). For a
discussion on why this is the case, see for example:

https://www.postgresql.org/message-id/flat/87oa4xmss7.fsf%40news-spur.riddles.org.uk#87oa4xmss7@news-spur.riddles.org.uk

So what is being reported at 40% bloat is probably actually not really
bloat.



--
Angular momentum makes the world go 'round.

Re: changing my mail address

[Ron Johnson]

On 03/17/2018 10:51 AM, Stephen Frost wrote:

Greetings,

* wambac...@posteo.de (wambac...@posteo.de) wrote:

how can i change my mail adress for the postgresql mailing lists? adding my
new address worked, but how do i get rid of the old one?

You'll need to change it on postgresql.org:

https://www.postgresql.org/account/

Once you've done that, log out of all PG sites (possibly by deleteing
cookies which you may have from them) and then log into postgresql.org
first and then go to lists.postgresql.org and the update should be
passed through.

Sadly, https://www.postgresql.org/account/reset/ hasn't worked for me in yearss.

--
Angular momentum makes the world go 'round.

Re: changing my mail address

[Ron Johnson]

On 03/17/2018 01:08 PM, Stephen Frost wrote:

Greetings,

* Ron Johnson (ron.l.john...@cox.net) wrote:

On 03/17/2018 10:51 AM, Stephen Frost wrote:

Once you've done that, log out of all PG sites (possibly by deleteing
cookies which you may have from them) and then log into postgresql.org
first and then go to lists.postgresql.org and the update should be
passed through.

Sadly, https://www.postgresql.org/account/reset/ hasn't worked for me in yearss.

That wasn't what the OP was asking about, but if you're having an issue
there, then please explain exactly what you're trying to do and what
issue you're seeing.  We're happy to try and resolve any such issues.


Having https://www.postgresql.org/account/reset/ send an password email to 
the address where I receive list emails hasn't worked for me in years.  
(Yes, I looked in the Spam folder, and the Inbox, and the folder where 
-general emails are filtered to.)


When I'd stopped using PG for a while, the simplest solution was to send 
them to the Trash.



--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

On 03/01/2018 02:32 PM, marcelo wrote:



On 01/03/2018 17:21 , Ron Johnson wrote:

On 03/01/2018 02:08 PM, marcelo wrote:



On 01/03/2018 16:42 , Ron Johnson wrote:

On 03/01/2018 01:11 PM, marcelo wrote:


On 01/03/2018 16:00 , Ron Johnson wrote:

[snip]
If your only unique index is a synthetic key, then you can insert the 
same "business data" multiple times with different synthetic keys.



--
Angular momentum makes the world go 'round.
IMHO, business logic can and must preclude "garbage insertion". Except 
you are inserting data directly to database using SQL, any n-tier 
architecture will be checking data validity.

bl
Any n-tier architecture that's bug-free.


Do you know about unit testing?


Way Back When Dinosaurs Still Roamed The Earth and I first learned the 
trade, the focus was on proper design instead of throwing crud against 
the wall and hoping tests caught any bugs.  Because, of course, unit 
tests are only as good as you imagination in devising tests.



So, you are fully convinced that there´s no bug free software... Same as I 
(and you) can code following the business rules, you (and me) can design 
unit tests not from "imagination" but from same rules.
Moreover: you can have a surrogate key (to speedup foreign keys) and 
simultaneously put a unique constraint on the columns requiring it. What´s 
the question?


Implementing tests to cover edge cases is much harder than implementing 
business rules in natural (and foreign) keys.



--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

On 03/01/2018 02:32 PM, David G. Johnston wrote:
On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson <ron.l.john...@cox.net 
<mailto:ron.l.john...@cox.net>>wrote:


Why have the overhead of a second unique index?  If it's "ease of
joins", then I agree with Francisco Olarte and use the business logic
keys in your joins even though it's a bit of extra work.


​The strongest case, for me, when a surrogate key is highly desirable is 
when there is no truly natural key and the best key for the model is 
potentially alterable. Specific, the "name" of something.  If I add myself 
to a database and make name unique, so David Johnston, then someone else 
comes along with the same name and now I want to add the new person as, 
say David A. Johnston AND rename my existing record to David G. Johnston.  
I keep the needed uniqueness ​and don't need to cobble together other data 
elements.  Or, if I were to use email address as the key the same physical 
entity can now change their address without me having to cascade update 
all FK instances too. Avoiding the FK cascade when enforcing a non-ideal 
PK is a major good reason to assign a surrogate.


There's always the "account number", which is usually synthetic. Credit Card 
numbers are also synthetic.  ICD numbers are (relatively) synthetic, too.


But that doesn't mean we have to use them willy-nilly everywhere.


--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

On 03/01/2018 02:44 PM, Daevor The Devoted wrote:



On Thu, Mar 1, 2018 at 10:32 PM, David G. Johnston 
<david.g.johns...@gmail.com <mailto:david.g.johns...@gmail.com>> wrote:


On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson <ron.l.john...@cox.net
<mailto:ron.l.john...@cox.net>>wrote:

Why have the overhead of a second unique index?  If it's "ease of
joins", then I agree with Francisco Olarte and use the business
logic keys in your joins even though it's a bit of extra work.


​The strongest case, for me, when a surrogate key is highly desirable
is when there is no truly natural key and the best key for the model
is potentially alterable.  Specific, the "name" of something.  If I
add myself to a database and make name unique, so David Johnston, then
someone else comes along with the same name and now I want to add the
new person as, say David A. Johnston AND rename my existing record to
David G. Johnston.  I keep the needed uniqueness ​and don't need to
cobble together other data elements.  Or, if I were to use email
address as the key the same physical entity can now change their
address without me having to cascade update all FK instances too.
Avoiding the FK cascade when enforcing a non-ideal PK is a major good
reason to assign a surrogate.

David J.


This is exactly my point: you cannot know when a Business Rule is going to 
change. Consider, for example, your Social Security number (or ID number 
as we call it in South Africa). This is unique, right?.


No, the SSN is not unique. 
https://www.computerworld.com/article/2552992/it-management/not-so-unique.html


Tomorrow, however, data of people from multiple countries gets added to 
your DB, and BAM! that ID number is suddenly no longer unique. Business 
Rules can and do change, and we do not know what may change in the future. 
Hence, it is safest to have the surrogate in place from the start, and 
avoid the potential migraine later on.


Disclaimer: this is just my opinion based on my experience (and the pain I 
had to go through when Business Rules changed). I have not done any 
research or conducted any studies on this.



--
Angular momentum makes the world go 'round.

Re: Version upgrade: is restoring the postgres database needed?

[Ron Johnson]

On 03/01/2018 11:28 AM, Melvin Davidson wrote:
[snip]


*>Hmmm.  I just looked at the script, and it says:

>$ pg_dumpall --schema-only > globals.sql

>That's not good.


*
*No that's actually correct.
pg_dumpall  can and will dump the globals
*
*pg_dump cannot*


I was invoking --schema-only and piping it to globals.sql.  That's deceptive.

--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

On 03/01/2018 01:05 PM, Melvin Davidson wrote:



On Thu, Mar 1, 2018 at 2:00 PM, Ron Johnson <ron.l.john...@cox.net 
<mailto:ron.l.john...@cox.net>> wrote:


On 03/01/2018 12:32 PM, Daevor The Devoted wrote:


[snip]



If your only unique index is a synthetic key, then you can insert the
same "business data" multiple times with different synthetic keys.



-- 
Angular momentum makes the world go 'round.



*If you are going to go to the trouble of having a surrogate/synthetic 
key, then you may as well have a primary key , which is much better. *


I completely agree.

--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

On 03/01/2018 02:09 PM, Daevor The Devoted wrote:



On Thu, Mar 1, 2018 at 9:00 PM, Ron Johnson <ron.l.john...@cox.net 
<mailto:ron.l.john...@cox.net>> wrote:


On 03/01/2018 12:32 PM, Daevor The Devoted wrote:



On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson <ron.l.john...@cox.net
<mailto:ron.l.john...@cox.net>> wrote:


On 03/01/2018 11:47 AM, Daevor The Devoted wrote:


On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar
<rakeshkumar...@aol.com <mailto:rakeshkumar...@aol.com>> wrote:


>Adding a surrogate key to such a table just adds overhead,
although that could be useful
>in case specific rows need updating or deleting without
also modifying the other rows with
>that same data - normally, only insertions and selections
happen on such tables though,
>and updates or deletes are absolutely forbidden -
corrections happen by inserting rows with
>an opposite transaction.

I routinely add surrogate keys like serial col to a table
already having a nice candidate keys
to make it easy to join tables.  SQL starts looking ungainly
when you have a 3 col primary
key and need to join it with child tables.


I was always of the opinion that a mandatory surrogate key (as
you describe) is good practice.
Sure there may be a unique key according to business logic
(which may be consist of those "ungainly" multiple columns), but
guess what, business logic changes, and then you're screwed!


And so you drop the existing index and build a new one.  I've
done it before, and I'll do it again.


So using a primary key whose sole purpose is to be a primary key
makes perfect sense to me.


I can't stand synthetic keys.  By their very nature, they're so
purposelessly arbitrary, and allow you to insert garbage into the
table.


Could you perhaps elaborate on how a surrogate key allows one to
insert garbage into the table? I'm afraid I don't quite get what
you're saying.


If your only unique index is a synthetic key, then you can insert the
same "business data" multiple times with different synthetic keys.


-- 
Angular momentum makes the world go 'round.




That might be where we're talking past each other: I do not advocate for 
the arbitrary primary key being the only unique index. Absolutely not. 
Whatever the business rules say is unique must also have unique indexes. 
If it's a business constraint on the data, it must be enforced in the DB 
(at least, that's how I try to do things).


Why have the overhead of a second unique index?  If it's "ease of joins", 
then I agree with Francisco Olarte and use the business logic keys in your 
joins even though it's a bit of extra work.


--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

On 03/01/2018 03:14 PM, Adrian Klaver wrote:

On 03/01/2018 01:03 PM, Ron Johnson wrote:

On 03/01/2018 02:32 PM, David G. Johnston wrote:


There's always the "account number", which is usually synthetic. Credit 
Card numbers are also synthetic. 


Actually, no:

https://en.wikipedia.org/wiki/Payment_card_number

There is a method to the madness, not just random issuance of numbers. It 
was made it relatively easy for folks to *generate numbers*. Hence the 
addition of CSC codes.


Right.  And how do the issuers generate the individual account identifier 
within their IIN ranges?




ICD numbers are (relatively)

synthetic, too.

But that doesn't mean we have to use them willy-nilly everywhere.


--
Angular momentum makes the world go 'round.

Re: Requiring pass and database psql shell command

[Ron Johnson]

The password file is also a solution.

https://www.postgresql.org/docs/current/static/libpq-pgpass.html

On 03/05/2018 07:33 AM, James Keener wrote:
Well, it's not a problem, it's the way it's designed and it's a sensible 
design. Check https://www.postgresql.org/docs/9.3/static/libpq-envars.html 
for more info on doing what you want.


On March 5, 2018 7:55:46 AM EST, "Łukasz Jarych"  wrote:

Hi Guys,

do you have also problem that every time you have to log into your
database using shell and psql?

I have to write over and over localhost, username, password...how to
force shell to remember this?

Best,
Jacek




--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

On 03/01/2018 12:32 PM, Daevor The Devoted wrote:



On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson <ron.l.john...@cox.net 
<mailto:ron.l.john...@cox.net>> wrote:



On 03/01/2018 11:47 AM, Daevor The Devoted wrote:


On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar <rakeshkumar...@aol.com
<mailto:rakeshkumar...@aol.com>> wrote:


>Adding a surrogate key to such a table just adds overhead,
although that could be useful
>in case specific rows need updating or deleting without also
modifying the other rows with
>that same data - normally, only insertions and selections happen
on such tables though,
>and updates or deletes are absolutely forbidden - corrections
happen by inserting rows with
>an opposite transaction.

I routinely add surrogate keys like serial col to a table already
having a nice candidate keys
to make it easy to join tables.  SQL starts looking ungainly when
you have a 3 col primary
key and need to join it with child tables.


I was always of the opinion that a mandatory surrogate key (as you
describe) is good practice.
Sure there may be a unique key according to business logic (which may
be consist of those "ungainly" multiple columns), but guess what,
business logic changes, and then you're screwed!


And so you drop the existing index and build a new one. I've done it
before, and I'll do it again.


So using a primary key whose sole purpose is to be a primary key
makes perfect sense to me.


I can't stand synthetic keys.  By their very nature, they're so
purposelessly arbitrary, and allow you to insert garbage into the table.


Could you perhaps elaborate on how a surrogate key allows one to insert 
garbage into the table? I'm afraid I don't quite get what you're saying.


If your only unique index is a synthetic key, then you can insert the same 
"business data" multiple times with different synthetic keys.



--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

On 03/01/2018 01:11 PM, marcelo wrote:


On 01/03/2018 16:00 , Ron Johnson wrote:

[snip]
If your only unique index is a synthetic key, then you can insert the 
same "business data" multiple times with different synthetic keys.



--
Angular momentum makes the world go 'round.
IMHO, business logic can and must preclude "garbage insertion". Except you 
are inserting data directly to database using SQL, any n-tier architecture 
will be checking data validity.


Any n-tier architecture that's bug-free.

--
Angular momentum makes the world go 'round.

Re: Version upgrade: is restoring the postgres database needed?

[Ron Johnson]

On 03/01/2018 11:46 AM, Adrian Klaver wrote:
[snip]

Hmmm.  I just looked at the script, and it says:

$ pg_dumpall --schema-only > globals.sql

That's not good.


Well it would dump the globals, but also the schema definitions for all 
the objects in the cluster. Though at this point we are only half way 
through the process. What is you restore procedure?


$ psql < globals.sql
$ pg_restore --clean --create --if-exists --exit-on-error --jobs=2 
${SRC}/${DB}.pgdump



--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

On 03/01/2018 11:47 AM, Daevor The Devoted wrote:


On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar > wrote:



>Adding a surrogate key to such a table just adds overhead, although
that could be useful
>in case specific rows need updating or deleting without also
modifying the other rows with
>that same data - normally, only insertions and selections happen on
such tables though,
>and updates or deletes are absolutely forbidden - corrections happen
by inserting rows with
>an opposite transaction.

I routinely add surrogate keys like serial col to a table already
having a nice candidate keys
to make it easy to join tables.  SQL starts looking ungainly when you
have a 3 col primary
key and need to join it with child tables.


I was always of the opinion that a mandatory surrogate key (as you 
describe) is good practice.
Sure there may be a unique key according to business logic (which may be 
consist of those "ungainly" multiple columns), but guess what, business 
logic changes, and then you're screwed!


And so you drop the existing index and build a new one.  I've done it 
before, and I'll do it again.


So using a primary key whose sole purpose is to be a primary key makes 
perfect sense to me.


I can't stand synthetic keys.  By their very nature, they're so 
purposelessly arbitrary, and allow you to insert garbage into the table.


--
Angular momentum makes the world go 'round.

Re: Version upgrade: is restoring the postgres database needed?

[Ron Johnson]

On 03/01/2018 10:37 AM, Vick Khera wrote:
On Thu, Mar 1, 2018 at 11:15 AM, Ron Johnson <ron.l.john...@cox.net 
<mailto:ron.l.john...@cox.net>> wrote:


No, I do:

$ pg_dump -Fc PROD > PROD.pgdump
$ pg_dump --globals-only postgres > globals.sql
$ pg_dump -Fc postgres > postgres.pgdump


That's how I back them up as well. You are correct that all you need to do 
is restore the globals.sql, then each "pgdump" file individually. Just 
ignore the warning when it tries to restore your initial postgres 
superuser, since it was created by the initdb already.


You probably don't need the "postgres" db at all, since it is just there 
to allow the client to connect to something on initial install. Normally 
you don't use it in production.


Good.  What, then, have I forgotten to restore such that the "Access 
privileges" are showing on my current 9.2 servers, but not on the 
newly-restored 9.6.6 server?


*Current*
postgres=# \l
   List of databases
    Name |  Owner   | Encoding |   Collate   | Ctype    |   Access 
privileges

-+--+--+-+-+---
CSSCAT_STI   | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 
CSS=CTc/CSS  +

 |  |  | | | =Tc/CSS  +
 |  |  | | | app_user=CTc/CSS
CSSCAT_STIB  | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 
CSS=CTc/CSS  +

 |  |  | | | =Tc/CSS  +
 |  |  | | | app_user=CTc/CSS
CSSCAT_STIC  | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 
CSS=CTc/CSS  +

 |  |  | | | =Tc/CSS  +
 |  |  | | | app_user=CTc/CSS

*Newly restored*
postgres=# \l
   List of databases
    Name |  Owner   | Encoding |   Collate   | Ctype    |   Access 
privileges

-+--+--+-+-+---
CSSCAT_STIB | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
CSSCAT_STIC | CSS  | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres    | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |


--
Angular momentum makes the world go 'round.

Re: Enforce primary key on every table during dev?

[Ron Johnson]

On 03/01/2018 11:07 AM, Steve Atkins wrote:
[snip]

"Every table should have a primary key, whether natural or surrogate"
is a great guideline, and everyone should follow it until they understand
when they shouldn't.


Most people think they know, but they don't.


--
Angular momentum makes the world go 'round.

Re: Schema/user/role

[Ron Johnson]

https://dba.stackexchange.com/questions/37012/difference-between-database-vs-user-vs-schema


On Mon, Mar 20, 2023 at 2:57 PM Bryn Llewellyn  wrote:

> > david.g.johns...@gmail.com wrote:
> >
> >> adapt...@comcast.net wrote:
> >>
> >> Is there any good reference to explain the best usage of each of these
> structures. I am coming from Oracle.  What is the best analog to Oracle's
> "user".
> >
> > A schema is a namespace mechanism for objects.  It has no relationship
> to roles aside from the possibility, if you so choose, to define a schema
> to have the same name as a role, in which case that schema becomes parts of
> that role's default search_path.
> >
> > There is no low-level difference between role and user.  A user is a
> role with the login privilege.
>
> I came from Oracle, too. I soon came to see that these facts about PG are
> an improvement on Oracle Database:
>
> — In ORCL, "user" and "role" are distinct notions but in PG they collapse
> into one. This means that the nodes in a PG role hierarchy can all own
> objects. And schemas are among these owned objects.
>
> — In ORCL, "user" and "schema" are 1:1 and so, informally, collapse into a
> single notion. In PG, a role can own many schemas and this can be used to
> advantage as a classification scheme for objects with the same owner.
>
> However, there's more to say.
>
> — "set role" (to a role upon which the current role is a grantee) has to
> be re-learned. For example, it cannot be governed by a required password.
> And it has the same effect on "current_role" (versus "session_role") that a
> "security definer" subprogram has (but with no push-pop notion).
>
> — It's not enough to say, for example, "grant select on table s1.t to r2"
> (when s1.t is owned by, say, r1 and the schema s1 is not owned by r2). You
> have, at a coarser granularity, to also say "grant usage on schema s1 to
> r2". (This is nice because you can prevent r2 from using any of r1's
> objects with just a single "revoke".)
>
> — The "search_path" notion sounds at first to be appealing. And, loosely,
> it makes up for the fact that PG has no synonym notion. However, just as in
> ORCL there's a whole discussion about how nefarious actors can capture a
> synonym with a bogus local object, so is there a similar discussion in PG
> about nefarious misuse of redefining the search path (no privilege governs
> this). This discussion is further complicated by the fact that "pg_temp"
> and "pg_catalog" are inevitably on the search path whether or not you
> mention them (and that when you don't, their positions in the search order
> is surprising). My personal conclusion is that you must always use a
> schema-qualified identifier for all objects in real application code
> (verbosity notwithstanding). This is rather like the ORCL practice never to
> create synonyms and to refer to ORCL-shipped objects as "sys.dbms_output"
> and the like.
>
> — Closely related, a freshly-created database has a "public" schema
> (unless you customize the "template1" database to change this. This is very
> useful for ad hoc testing when you're learning something, But it's a
> nuisance in the database that a serious application uses.
>
> — Having said this, a caveat is probably needed for "pg_catalog" objects
> because even common-or-garden objects like the "+" operator are implemented
> ordinarily via various objects in the "pg_catalog" schema. And the syntax
> for invoking an operator using a schema-qualified identifier is baroque:
>
> select ((2+3) operator(pg_catalog.=) (1+4))::text;
>
> I decided, eventually, to use schema-qualified identifiers for everything
> except for "pg_catalog" objects and always to set the search path thus:
>
> set search_path = pg_catalog, pg_temp;
>
> and especially always to use that list as an attribute in a subprogram's
> source code.
>
>
>
>

Re: Schemas and Search Path

[Ron Johnson]

Please don't hijack threads.

https://www.postgresql.org/docs/13/limits.html
No mention of schemas.

On Mon, Mar 20, 2023 at 12:08 PM DAVID ROTH  wrote:

> Is there any practical limit on the number of schemas in a database?
> Will the number of schemas in a user's search path impact performance?
>
> Thanks
> Dave Roth
>
> On 03/20/2023 10:15 AM Ron Johnson  wrote:
>
>
> Real-time CDC is the difficult part.  ora2pg (using views) can do a static
> migration.  No coding (unless you consider clever use of bash to modify
> config files to be coding).  I used it to migrate a 7TB db to Postgresql.
>
> https://ora2pg.darold.net/
>
> On Mon, Mar 20, 2023 at 8:58 AM Inzamam Shafiq 
> wrote:
>
> Hi,
>
> Hope everyone is fine.
>
> Can someone help or guide regarding Open Source tools for Oracle to
> PostgreSQL migration with real time CDC. along with this is there any
> possibility to change the structure of the database? Let me explain a
> little more,
>
> We have an Oracle DB which is around 1TB and we want to migrate to
> PostgreSQL that have a new table structure, so we want to perform data
> transformation and real time CDC from Oracle to PostgreSQL. Do we have any
> good open source tool to achieve this with No Coding involved.??
>
> Thanks.
>
> Regards,
>
> *Inzamam Shafiq*
> *Sr. DBA*
>
>

Re: Oracle to PostgreSQL Migration

[Ron Johnson]

Real-time CDC is the difficult part.  ora2pg (using views) can do a static
migration.  No coding (unless you consider clever use of bash to modify
config files to be coding).  I used it to migrate a 7TB db to Postgresql.

https://ora2pg.darold.net/

On Mon, Mar 20, 2023 at 8:58 AM Inzamam Shafiq 
wrote:

> Hi,
>
> Hope everyone is fine.
>
> Can someone help or guide regarding Open Source tools for Oracle to
> PostgreSQL migration with real time CDC. along with this is there any
> possibility to change the structure of the database? Let me explain a
> little more,
>
> We have an Oracle DB which is around 1TB and we want to migrate to
> PostgreSQL that have a new table structure, so we want to perform data
> transformation and real time CDC from Oracle to PostgreSQL. Do we have any
> good open source tool to achieve this with No Coding involved.??
>
> Thanks.
>
> Regards,
>
> *Inzamam Shafiq*
> *Sr. DBA*
>

Re: unbale to list schema

[Ron Johnson]

On Wed, Jan 17, 2024 at 1:46 PM Atul Kumar  wrote:

> Hi,
>
> I am not able to find any solution to list all schemas in all databases at
> once, to check the structure of the whole cluster.
>
> As I need to give a few privileges to a user to all databases, their
> schemas and schemas' objects (tables sequences etc.).
>
> Please let me know if there is any solution/ query that will serve the
> purpose.
>

Is this what you are looking for?

 #!/bin/bash
declare DbHost=
declare DB=
declare Schemas="select schema_name from information_schema.schemata
 where schema_name not like 'pg_%' and schema_name !=
'information_schema';"
for s in $(psql --host=$DbHost --dbname=$DB -AXtc "${Schemas}")
do
pg_dump --dbname=$DB --schema-only --schema=${s} >
schema_${DbHost}_${DB}_${s}.sql
done

Re: Backup certain months old data

[Ron Johnson]

On Mon, Jan 22, 2024 at 10:12 PM Siraj G  wrote:

> Hello!
>
> I would like to know how we can backup certain months old data from PgSQL
>

Use the COPY command.


> and then delete it.
>

That would depend on how many records, how big the records are, and if
there's index support on the "date" field.


> The intent is to backup everything that is older than 2 quarters to a blob
> storage and delete it, to improve performance and reduce billing.
>

I had to do something similar for my previous employer.

1. Used COPY to dump the old data.
2. CREATE INDEX i_foo_sd1 ON foo (some_date);
3. DELETE FROM foo WHERE some_date BETWEEN x AND y;  When there wasn't a
lot of data, it was the whole month.  When there was a lot of data, I
looped through it one day at a time..
4. DROP INDEX i_foo_sd1;

It was a bash script that reads a text file, where each row is a
tab-delimited record with table name and column,

Re: Mimic ALIAS in Postgresql?

[Ron Johnson]

On Mon, Jan 22, 2024 at 6:40 PM Rob Sargent  wrote:

> On 1/17/24 16:25, Jim Nasby wrote:
>
> On 1/16/24 6:41 PM, Rob Sargent wrote:
>
> On 1/16/24 17:39, Jim Nasby wrote:
>
> On 1/16/24 4:57 PM, Rob Sargent wrote:
>
> Or perhaps you have to beef the sed up to use word boundaries just
> in case.
>
>
> I'm not a Java web developer... 
>
>
> You need to adjust you glasses if that's what you see me as.
>
>
> Reality is that basically all modern (as in last 20 years) SQL access is
> via frameworks that all use their own language and come up with SQL based
> on that. How hard it'd be to bulk change the schema depends entirely on the
> framework.
>
> Hm, it's a string /somewhere/.  The rest of this thread might be accused
> of adding to the problem.
>
>
> No, it's not, at least not as a complete SQL statement. See [1] as an
> example of how this works in Ruby on Rails. Most modern frameworks work in
> a similar fashion: you DON'T write raw SQL, or anything that looks anything
> like it. In fact, many (most?) of these frameworks make it difficult to do
> anything in raw SQL because it completely breaks the paradigm of the
> framework.
>
> Note that I'm talking about *frameworks*, not languages. But since most
> languages require huge amounts of boilerplate to create a web service or
> website it's not surprising that pretty much everyone uses frameworks. (Go
> is actually an interesting exception to this.)
>
> 1: https://guides.rubyonrails.org/active_record_querying.html#find
>
> You may well be correct, but I have to ask the OP (Ron) if this is the
> case in the current situation.  I find it difficult to conceive of a
> "framework" apparently arbitrarily flipping between the alias and the base
> name. (I read "For example, sometimes" as arbitrarily.)  The few database
> frameworks with which I'm familiar would tolerate the coder using either
> name.  And indeed in those (hibernate, mybatis, jOOQ) the coder would be
> the one choosing the /nom du jour/.
>

I don't know what, if any, framework the developer uses.

Re: Question on partitioning

[Ron Johnson]

On Sun, Feb 4, 2024 at 4:40 PM veem v  wrote:

> Hello All,
> In postgresql, Is it possible to partition an existing nonpartitioned
> table having data already residing in it and indexes and constraints
> defined in it, without the need of manually moving the data around, to make
> it faster? Similarly merging multiple partitions to one partition or
> splitting a single partition into multiple partitions?
>

That depends on how you partition the table, and what the PK is.  Works
great when the PK is constantly increasing (think "sequence" or
"timestamp", and you partition by PK ranges; not so great when you want to
partition by a different column.

Re: How to do faster DML

[Ron Johnson]

On Sat, Feb 3, 2024 at 11:09 AM Lok P  wrote:
 [snip]

> show maintenance_work_mem; - 4155MB
> show work_mem; - 8MB
> show shared_buffers ; -22029684
>

Those are pretty small values.  What are your server specs?

Re: How to do faster DML

[Ron Johnson]

On Mon, Feb 12, 2024 at 3:23 PM veem v  wrote:
[snip]

> So it looks like the fixed length data type(like integer, float) should be
> the first choice while choosing the data type of the attributes
> wherever possible, as these are native types.
>

Correct.


> (Like choosing "Integer/float" over "Numeric", "Char" over "Varchar" etc).
>
However I do see even in Oracle databases, we have Integer type too, but
> it's suggesting(For e.g. in below blog) to rather go with Number types over
> Integer and Varchar2 over Char, which is opposite of what we are discussing
> here. Is the fixed length data type behaves differently in postgres vs
> oracle and thus should be treated differently?
>
> https://www.databasestar.com/oracle-data-types/
>

>
> From above blog:-
>

Oracle is not Postgresql.  WTH are you using an Oracle blog to decide on
Postgresql data types


>
>
> *When to use CHAR: There should be no reason to use the CHAR data type, as
> it is similar to a VARCHAR2 and it’s better to be consistent.*
> *When to use INTEGER: You should use the NUMBER data type instead.*
>
>
Did you actually read that blog post?

Have you even read the Postgresql documentation on data types?

Re: Postgres pg_cron extension

[Ron Johnson]

On Wed, Feb 14, 2024 at 10:23 AM Greg Sabino Mullane 
wrote:

> Yes, definitely the wrong forum. RDS is not Postgres, and "parameter
> groups" is an Amazon thing. A quick web search would seem to indicate that
> the default group is replaced by the custom one, rather than enhancing it.
> But really, this is a simple "try it and see" sort of task.
>
>
> https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/parameter-groups-overview.html
>

Parameter Groups are AWS' name for, and clunky web implementation of,
postgresql.conf.  You can use the same Parameter Group for multiple
instances, or you can create a different PaGr for each instance.

(Modifying a PaGr via the aws cli made it tolerably useful.)

Re: PITR

[Ron Johnson]

On Wed, Feb 14, 2024 at 1:39 PM Yongye Serkfem  wrote:

> Hi,
> I hope you are all doing well. I am trying to do PITR on Postgresql v12.
> Now that the recovery.conf file is not available in this version, where
> should I set the recovery_target_time? I checked the Postgresql.conf file
> and can't find the appropriate parameter to set the target time.
>

Consider using PgBackRest.  It's multithreaded, and you specify the
"restore-from start point" and "recover until" time on the command line.
It then handles everything for you, besides the final "pg_ctl start -D ...".

Re: How to do faster DML

[Ron Johnson]

On Wed, Feb 14, 2024 at 11:58 PM veem v  wrote:

>
>
> On Thu, 15 Feb 2024 at 00:43, Adrian Klaver 
> wrote:
>
>> It depends:
>>
>> https://www.postgresql.org/docs/current/sql-altertable.html
>>
>> "Adding a column with a volatile DEFAULT or changing the type of an
>> existing column will require the entire table and its indexes to be
>> rewritten. As an exception, when changing the type of an existing column,
>> if the USING clause does not change the column contents and the old type
>> is either binary coercible to the new type or an unconstrained domain over
>> the new type, a table rewrite is not needed. However, indexes must always
>> be rebuilt unless the system can verify that the new index would be
>> logically equivalent to the existing one. For example, if the collation for
>> a column has been changed, an index rebuild is always required because the
>> new sort order might be different. However, in the absence of a collation
>> change, a column can be changed from text to varchar (or vice versa)
>> without rebuilding the indexes because these data types sort identically.
>> Table and/or index rebuilds may take a significant amount of time for a
>> large table; and will temporarily require as much as double the disk space.
>>
>> "
>>
>>
>> create table int_test(int_fld integer);
>>
>> insert into int_test select * from generate_series(1, 1, 1);
>>
>>
>> select ctid, int_fld from int_test ;
>>
>> ctid   | int_fld
>> --+-
>>  (0,1)|   1
>>  (0,2)|   2
>>  (0,3)|   3
>>  (0,4)|   4
>>  (0,5)|   5
>>  (0,6)|   6
>>  (0,7)|   7
>>  (0,8)|   8
>>  (0,9)|   9
>>  (0,10)   |  10
>>
>>
>> alter table int_test alter column int_fld set data type bigint;
>>
>> select ctid, int_fld from int_test ;
>>
>>   ctid   | int_fld
>> --+-
>>  (0,1)|   1
>>  (0,2)|   2
>>  (0,3)|   3
>>  (0,4)|   4
>>  (0,5)|   5
>>  (0,6)|   6
>>  (0,7)|   7
>>  (0,8)|   8
>>  (0,9)|   9
>>  (0,10)   |  10
>>
>> update int_test set  int_fld = int_fld;
>>
>> select ctid, int_fld from int_test  order by int_fld;
>>
>> (63,1)|   1
>>  (63,2)|   2
>>  (63,3)|   3
>>  (63,4)|   4
>>  (63,5)|   5
>>  (63,6)|   6
>>  (63,7)|   7
>>  (63,8)|   8
>>  (63,9)|   9
>>  (63,10)   |  10
>>
>>
>> Where ctid is:
>>
>> https://www.postgresql.org/docs/current/ddl-system-columns.html
>>
>>
>>  Thank you so much.
> So as I also tested the same as you posted, there has been no change in
> "ctid" , when I altered the column data type from 'int' to 'bigint' in the
> table, so that means full table rewriting won't happen in such a scenario.
>

It happened when I altered columns from INTEGER to BIGINT.  How do I know?

The disk filled up.

>

Re: Compressing large column by moving it to a unique table

[Ron Johnson]

1. Show us the PG version, view definition, the exact query that's slow,
and the EXPLAIN (ANALYZE).
2. Presumably there's an index on each table's *_hash column?

On Tue, Feb 13, 2024 at 8:48 AM Adrian Garcia Badaracco 
wrote:

> Thank you for the reply Ron.
>
> Yes there are many fewer (<1%) the number of rows in new_table.
>
> Thanks for making me think of normalization, I hadn’t seen it that way.
> Although there is no theoretical relationship between the rows in the other
> columns in the original table and the attributes column, in practice there
> is a strong correlation, so I guess what I am trying to capture here is
> taking advantage of that correlation, while not completely depending on it
> because it can be broken.
>
> In any case, whatever theoretical framework is put around this solution, I
> am also interested in the practical aspects, in particular that case of
> selecting a subset of columns from the view that I know doesn’t need the
> join but the query planner thinks does.
>
> On Tue, Feb 13, 2024 at 3:16 AM Ron Johnson 
> wrote:
>
>> On Mon, Feb 12, 2024 at 10:12 PM Adrian Garcia Badaracco <
>> adr...@adriangb.com> wrote:
>>
>>> I am using Timescale so I'll be mentioning some timestamp stuff but I
>>> think this is a general postgres question for the most part.
>>>
>>> I have a table with some fixed, small columns (id, timestamp, etc) and a
>>> large JSONB column (let's call it `attributes`). `attributes` has 1000s of
>>> schemas, but given a schema, there's a lot of duplication. Across all rows,
>>> more than 99% of the data is duplicated (as measured by `count(attributes)`
>>> vs `count(distinct attributes)`.
>>>
>>> I can't normalize `attributes` into real columns because it is quite
>>> variable (remember 1000s of schemas).
>>>
>>> My best idea is to make a table like `(day timestamptz, hash text,
>>> attributes jsonb)` and then in my original table replace `attributes` with
>>> a reference to `new_table`.
>>>
>>
>> Meaning that there are many fewer rows in new_table?
>>
>>
>>> I can then make a view that joins them `select original_table.timestamp,
>>> new_table.attributes from original join new_table on (time_bucket('1 day',
>>> timestamp) = day AND original.hash = new_table.hash)` or something like
>>> that. The idea of time bucketing into 1 day is to balance write and read
>>> speed (by relying on timescale to do efficient time partitioning, data
>>> retention, etc.).
>>>
>>
>>> I recognize this is essentially creating a key-value store in postgres
>>> and also janky compression, so I am cautious about it.
>>>
>>
>> If my interpretation (that there are many fewer rows in new_table) is
>> correct, then you've stumbled into the Second Normal Form of database
>> design: https://en.wikipedia.org/wiki/Second_normal_form#Example
>>
>>

Re: Compressing large column by moving it to a unique table

[Ron Johnson]

On Mon, Feb 12, 2024 at 10:12 PM Adrian Garcia Badaracco <
adr...@adriangb.com> wrote:

> I am using Timescale so I'll be mentioning some timestamp stuff but I
> think this is a general postgres question for the most part.
>
> I have a table with some fixed, small columns (id, timestamp, etc) and a
> large JSONB column (let's call it `attributes`). `attributes` has 1000s of
> schemas, but given a schema, there's a lot of duplication. Across all rows,
> more than 99% of the data is duplicated (as measured by `count(attributes)`
> vs `count(distinct attributes)`.
>
> I can't normalize `attributes` into real columns because it is quite
> variable (remember 1000s of schemas).
>
> My best idea is to make a table like `(day timestamptz, hash text,
> attributes jsonb)` and then in my original table replace `attributes` with
> a reference to `new_table`.
>

Meaning that there are many fewer rows in new_table?


> I can then make a view that joins them `select original_table.timestamp,
> new_table.attributes from original join new_table on (time_bucket('1 day',
> timestamp) = day AND original.hash = new_table.hash)` or something like
> that. The idea of time bucketing into 1 day is to balance write and read
> speed (by relying on timescale to do efficient time partitioning, data
> retention, etc.).
>

> I recognize this is essentially creating a key-value store in postgres and
> also janky compression, so I am cautious about it.
>

If my interpretation (that there are many fewer rows in new_table) is
correct, then you've stumbled into the Second Normal Form of database
design: https://en.wikipedia.org/wiki/Second_normal_form#Example

Re: MAT. VIEW security problems and PG 10-11 versions?

[Ron Johnson]

On Tue, Feb 13, 2024 at 3:44 AM Daniel Gustafsson  wrote:

> > On 13 Feb 2024, at 08:56, Durumdara  wrote:
>
> > But maybe that's because PG 10 and 11 are no longer supported - and not
> because they aren't affected by the issues.
>
> EOL versions do not recieve security updates and are not verified during
> security analysis and fixing, so hence they aren't listed.  Anyone still
> running 10 or 11 is encouraged to upgrade to a supported version.
>

I wonder if MSFT and Oracle test EOL versions of their software for
discovered bugs.   Maybe, but I doubt it, for the very same reason the PGDG
doesn't do it.

But even if they do... how far back would they go?  Unless they go back to
Windows 1.0, and Oracle 1.0, _someone_ would complain.

Re: Compressing large column by moving it to a unique table

[Ron Johnson]

a-7816-4f8d-9fd7-24388c08978c
>
> explain (analyze, buffers, verbose, settings, format json)
> SELECT start_timestamp
> FROM original
> WHERE (
>   start_timestamp > ('2024-01-01 00:00:00.00+00'::timestamptz + interval
> '1 day')
>   AND
>   start_timestamp < ('2024-01-01 00:00:00.00+00'::timestamptz + interval
> '2 day')
> );
> --
> https://app.pgmustard.com/#/explore/f05de3e8-ca8d-4404-ad33-69f103a3b85a
>
>
> -- A relatively selective query
> explain (analyze, buffers, verbose, settings, format json)
> SELECT *
> FROM joined_view
> WHERE (
>   get_byte(attributes, 4) < 100
>   AND
>   start_timestamp > ('2024-01-01 00:00:00.00+00'::timestamptz + interval
> '1 day')
>   AND
>   start_timestamp < ('2024-01-01 00:00:00.00+00'::timestamptz + interval
> '2 day')
> );
> --
> https://app.pgmustard.com/#/explore/a75b20bd-07d5-4402-a0d8-22419682307a
>
> explain (analyze, buffers, verbose, settings, format json)
> SELECT *
> FROM original
> WHERE (
>   get_byte(attributes, 4) < 100
>   AND
>   start_timestamp > ('2024-01-01 00:00:00.00+00'::timestamptz + interval
> '1 day')
>   AND
>   start_timestamp < ('2024-01-01 00:00:00.00+00'::timestamptz + interval
> '2 day')
> );
> --
> https://app.pgmustard.com/#/explore/47ef84f0-a96e-4baa-af40-2ec241cbb6e2
> ```
>
> On Tue, Feb 13, 2024 at 11:04 AM Ron Johnson 
> wrote:
>
>> 1. Show us the PG version, view definition, the exact query that's slow,
>> and the EXPLAIN (ANALYZE).
>> 2. Presumably there's an index on each table's *_hash column?
>>
>> On Tue, Feb 13, 2024 at 8:48 AM Adrian Garcia Badaracco <
>> adr...@adriangb.com> wrote:
>>
>>> Thank you for the reply Ron.
>>>
>>> Yes there are many fewer (<1%) the number of rows in new_table.
>>>
>>> Thanks for making me think of normalization, I hadn’t seen it that way.
>>> Although there is no theoretical relationship between the rows in the other
>>> columns in the original table and the attributes column, in practice there
>>> is a strong correlation, so I guess what I am trying to capture here is
>>> taking advantage of that correlation, while not completely depending on it
>>> because it can be broken.
>>>
>>> In any case, whatever theoretical framework is put around this solution,
>>> I am also interested in the practical aspects, in particular that case of
>>> selecting a subset of columns from the view that I know doesn’t need the
>>> join but the query planner thinks does.
>>>
>>> On Tue, Feb 13, 2024 at 3:16 AM Ron Johnson 
>>> wrote:
>>>
>>>> On Mon, Feb 12, 2024 at 10:12 PM Adrian Garcia Badaracco <
>>>> adr...@adriangb.com> wrote:
>>>>
>>>>> I am using Timescale so I'll be mentioning some timestamp stuff but I
>>>>> think this is a general postgres question for the most part.
>>>>>
>>>>> I have a table with some fixed, small columns (id, timestamp, etc) and
>>>>> a large JSONB column (let's call it `attributes`). `attributes` has 1000s
>>>>> of schemas, but given a schema, there's a lot of duplication. Across all
>>>>> rows, more than 99% of the data is duplicated (as measured by
>>>>> `count(attributes)` vs `count(distinct attributes)`.
>>>>>
>>>>> I can't normalize `attributes` into real columns because it is quite
>>>>> variable (remember 1000s of schemas).
>>>>>
>>>>> My best idea is to make a table like `(day timestamptz, hash text,
>>>>> attributes jsonb)` and then in my original table replace `attributes` with
>>>>> a reference to `new_table`.
>>>>>
>>>>
>>>> Meaning that there are many fewer rows in new_table?
>>>>
>>>>
>>>>> I can then make a view that joins them `select
>>>>> original_table.timestamp, new_table.attributes from original join 
>>>>> new_table
>>>>> on (time_bucket('1 day', timestamp) = day AND original.hash =
>>>>> new_table.hash)` or something like that. The idea of time bucketing into 1
>>>>> day is to balance write and read speed (by relying on timescale to do
>>>>> efficient time partitioning, data retention, etc.).
>>>>>
>>>>
>>>>> I recognize this is essentially creating a key-value store in postgres
>>>>> and also janky compression, so I am cautious about it.
>>>>>
>>>>
>>>> If my interpretation (that there are many fewer rows in new_table) is
>>>> correct, then you've stumbled into the Second Normal Form of database
>>>> design: https://en.wikipedia.org/wiki/Second_normal_form#Example
>>>>
>>>>

Re: Query hangs (and then timeout) after using COPY to import data

[Ron Johnson]

On Sun, Feb 11, 2024 at 4:41 PM Adrian Klaver 
wrote:

> On 2/11/24 13:37, ste...@gmail.com wrote:
>
[snip]

> >
> > The same query, executed from pgAdmin, returns the result in less than a
> > second (even if it’s executed while the query from my app is running).
> >
> > (actually the result are 0 record, but it’s correct: the query it’s just
> > a validation that there are no records in that query)
>
[snip]

> > Do you have any suggestions to solve this problem?
> >
>
> Run ANALYZE on the tables/database.
>

If ANALYZE was the cure, why does it successfully run from PgAdmin?

Re: How to do faster DML

[Ron Johnson]

On Sun, Feb 11, 2024 at 11:54 AM veem v  wrote:
[snip]

> When you said *"you would normally prefer those over numeric " *I was
> thinking the opposite. As you mentioned integer is a fixed length data type
> and will occupy 4 bytes whether you store 15 or .But in case of
> variable length type like Number or numeric , it will resize itself based
> on the actual data, So is there any downside of going with the variable
> length data type like Numeric,
>

Consider a table with a bunch of NUMERIC fields.  One of those records has
small values (aka three bytes).  It fits neatly in 2KiB.

And then you update all those NUMERIC fields to big numbers that take 15
bytes.  Suddenly (or eventually, if you update them at different times),
the record does *not* fit in 2KiB, and so must be moved to its own.page.
That causes extra IO.


> Varchar type always for defining the data elements?
>

Internally, all character-type fields are stored as TEXT.  CHAR and
VARCHAR(XX)'s only purposes are SQL-compliance and length-limitation.
And length-limitation is "just" a legacy carried forward from the card
punch days.

Re: Query hangs (and then timeout) after using COPY to import data

[Ron Johnson]

Since the query works in PgAdmin, but not in npgsql, the problem has to be
somewhere in Npgsql.

https://www.npgsql.org/doc/diagnostics/overview.html

Maybe increasing the log level will lead to a solution.

On Sun, Feb 11, 2024 at 6:13 PM  wrote:

> Thanks, Adrian, for the suggestion, but same problem.
>
> I've just tried to execute "ANALYZE" (on the whole database) after the
> import of all the tables (with COPY) and before the other queries, but the
> query always hangs (I hope this was the way you suggested).
>
> Stefano
>
> > -Original Message-
> > From: Adrian Klaver 
> > Sent: Sunday, February 11, 2024 10:42 PM
> > To: ste...@gmail.com; pgsql-general@lists.postgresql.org
> > Subject: Re: Query hangs (and then timeout) after using COPY to import
> data
> >
> > On 2/11/24 13:37, ste...@gmail.com wrote:
> > > Hello,
> > >
> > > I’m new to PostgreSQL. I’m trying to migrate an app from SqlServer to
> > > Postgres (it’s written in C# and uses Npgsql)
> > >
> > > I’ve tried with Postgres 16.1 and 16.2 on Windows Server 2019.
> > >
> > > This app used SqlServer’s Bulk Insert to import some tables (about 50
> > > tables) from another database, I replaced it with Postgres’ COPY
> > > function: this part works correctly.
> > >
> > > After the import, I execute sequentially (not in parallel) some
> > > queries in these tables, to update some data and to make some
> validations.
> > >
> > > At some point, systematically, one of these queries hangs, and after
> > > 10 minutes (the CommandTimeout that I set) it throws this exception:
> > >
> > > Exception while reading from stream ---> System.TimeoutException:
> > > Timeout during reading attempt
> > >
> > > at Npgsql.Internal.NpgsqlConnector
> > >
> > > The query is this one:
> > >
> > >
> > > SELECT Id FROM Item
> > >
> > > WHERE Id NOT IN (
> > >
> > > SELECT ItemId FROM ItemUom)
> > >
> > > LIMIT 100
> > >
> > > The same query, executed from pgAdmin, returns the result in less than
> > > a second (even if it’s executed while the query from my app is
> running).
> > >
> > > (actually the result are 0 record, but it’s correct: the query it’s
> > > just a validation that there are no records in that query)
> > >
> > > While the query is running from my app, I noticed that the CPU goes
> > > beyond 95%, even up to 100%, due to 3 postgres.exe processes.
> > >
> > > The RAM usage is less than 70%.
> > >
> > > In pgAdmin I’ve executed a query to list the running queries, and I
> > > can see that one.
> > >
> > > My issue seems to be very similar to this one:
> > > https://stackoverflow.com/questions/77195107/npgsql-timeout-during-
> > rea
> > > ding-attempt
> > >  > re
> > > ading-attempt> but I didn’t understand how that guy solved the
> > > problem.
> > >
> > > If I import less tables from the external database, the query doesn’t
> > > hang and runs correctly, so this make me think about some resources
> > > that could “finish”, but I haven’t understood which one (for example
> > > the connections used to import the tables and all the commands and
> > > datareader used to execute the queries seem disposed correctly).
> > >
> > > I don’t know if it could be due to some Postgres parameter.
> > >
> > > Do you have any suggestions to solve this problem?
> > >
> >
> > Run ANALYZE on the tables/database.
> >
> > See:
> > https://www.postgresql.org/docs/current/sql-analyze.html
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com
>
>
>
>

Re: How to do faster DML

[Ron Johnson]

On Tue, Feb 13, 2024 at 4:17 PM veem v  wrote:
[sni[]

> One question here, if we have defined one column as a fixed length data
> type "integer" and slowly we noticed the length of data keeps increasing
> (in case of a sequence generated PK column which will keep increasing), and
> we want to alter the column to "bigint" now. In such scenario, will it
> append/pad the additional spaces to all the existing values which were
> already stored  with integer type initially in the table. And that would be
> then an increase to the existing table storage. Please correct me if I'm
> wrong.
>

ALTER TABLE foo ALTER COLUMN bar BIGINT; rewrites the whole table.  Do
yourself a favor, and start with BIGINT.

Re: Encryption Options

[Ron Johnson]

The phrases "personal information" and "data at rest encryption" strongly
indicate PCI, or something similar.

On Fri, Feb 16, 2024 at 12:20 PM Greg Sabino Mullane 
wrote:

> You need to clearly define your threat model. What exactly are you
> defending against? What scenario do you want to avoid?
>
> Also, your decision of on-premise or Aurora is extremely relevant to your
> range of options.
>
> Cheers,
> Greg
>
>

Re: Encryption Options

[Ron Johnson]

On Fri, Feb 16, 2024 at 1:53 AM sud  wrote:

> Hello Friends,
>
> We are newly moving to postgres database (yet to decide if it would be an
> on premise one or AWS aurora postgres). However ,  we want to understand
> what  encryption / decryption techniques are available in the postgres
> database.
>
> We may have some sensitive/"personal information" (like customer name,
> account number etc )stored in the database
>

The problem with encrypting "account number" is that you can't JOIN or
WHERE on it. That's not always necessary, though.  The pgcrypto module does
what it says, but requires application-level changes,

Encryption at rest can be accomplished with filesystem-level encryption,
and backup encryption.  (PgBackRest has that feature, using AES-256.  Don't
know about BarMan.)

Re: Encryption Options

[Ron Johnson]

On Fri, Feb 16, 2024 at 4:04 PM sud  wrote:

> On Fri, Feb 16, 2024 at 10:50 PM Greg Sabino Mullane 
> wrote:
>
>> You need to clearly define your threat model. What exactly are you
>> defending against? What scenario do you want to avoid?
>>
>> Also, your decision of on-premise or Aurora is extremely relevant to your
>> range of options.
>>
>>
> Thank you.
>
> Yes these are Account number/PCI data and "data at rest" encryption is
> something management is asking to have irrespective of whether we encrypt
> those before storing in the database or not. And this system needs to
> adhere to PCI 4.0 standards , so it looks like we can't persist the PCI
> data as is in th database even if the 'data at rest' encryption is there,
> it has to be encrypted before storing into the database.
> https://www.varonis.com/blog/pci-dss-requirements
>
> Agreed. The on-premise vs aurora will take a different approach for
> catering to above needs. We are currently evaluating , what would be the
> possible options in each of these cases? and if this would be a factor in
> choosing the on-premise postgres vs aurora postgres?
>
> On Sat, Feb 17, 2024 at 12:40 AM Ron Johnson 
> wrote:
>
>> The problem with encrypting "account number" is that you can't JOIN or
>> WHERE on it. That's not always necessary, though.  The pgcrypto module does
>> what it says, but requires application-level changes,
>>
>> Encryption at rest can be accomplished with filesystem-level encryption,
>> and backup encryption.  (PgBackRest has that feature, using AES-256.  Don't
>> know about BarMan.)
>>
>>
> Will try to verify these options. Considering these system processes 100's
> of millions of transactions
>

Per minute?  Hour?  Day?  Month?  Year?  Decade?

Continuous?  In waves?

, will these encryption add significant overhead?
>

"Significant" is relative, and depends on the CPU.


> It would be great, if you can suggest some doc to follow, for implementing
> these.
>

Google "pgcrypto".


> Not sure if the same would work for aurora too.
>

This list avoids giving help on Aurora, since it's very different from
Postgresql. AWS *RDS Postgresql* is much closer to vanilla Postgresql, so
we can help with that.

Exclude certain application pgaudit logging?

[Ron Johnson]

Currently, we use Object audit logging to capture all READ access to
columns FOO_1, FOO_2 and FOO_3 in table BAR.SCRABBLE.  (They are the three
columns have PII data.)

The problem is that the application legitimately reads these columns
thousands of times per day.  Thus, the log fills up with meaningless data
that swamps any legitimate invalid accesses.

Thus, I'd like to exclude reads from "Postgresql JDBC Driver".  (Currently,
I filter that out using "grep -v" in a shell script that runs hourly from
cron, but I find that unsatisfactory.)

Re: Question on partitioning

[Ron Johnson]

On Tue, Feb 6, 2024 at 2:40 PM veem v  wrote:

> Thank you Laurenz. Got it.
>
> So basically , you mean to say any DDL on a table won't allow the table to
> be read by other processes. I was under the assumption that it should allow
> the read queries to move ahead at least. I must be wrong here. Thanks for
> correcting me.
>

That word "any" in "any DDL" is quite a big word.  It's certainly not going
to allow reads while you're adding a Foreign Key.

And the whole purpose of adding the CONCURRENTLY key word to CREATE INDEX
is because regular CREATE INDEX statements block everyone else.

https://www.postgresql.org/docs/16/sql-createindex.html
Look for the keyword CONCURRENTLY in
https://www.postgresql.org/docs/current/sql-altertable.html

Re: Safest pgupgrade jump distance

[Ron Johnson]

On Mon, Feb 12, 2024 at 5:08 AM Dávid Suchan 
wrote:

> Hi, I was wondering what is the safest pg_upgrade version upgrade distance
> going from 9.6 version. Do I need to go version by version or I can go from
> 9.6 to 15? We have a very huge database(TBs) with one replication server,
> so we will first run the pgupgrade on the main server and then rsync to a
> standby replica. I'm not sure whether it's safe to do it from 9.6 to 15 at
> once


https://www.postgresql.org/docs/16/pgupgrade.html
"pg_upgrade supports upgrades from 9.2.X and later to the current major
release of PostgreSQL, including snapshot and beta releases."

vacuumdb did not analyze all tables?=

[Ron Johnson]

  vacuumdb 15.3
database instance: 9.6.24

I manually analyzed 71 tables this morning at 10:42.  (All those with
"rp20_y2021" in the relname.)

Three of the 71 tables were not analyzed.  Why would that be?

(Five were not vacuumed, but I accept that some other process might have
blocked them.)

vacuumdb -U postgres -h $DbServer --analyze -j6 -t ... -t
cds.cdstransaction_rp20_y2021 -t ...

TAPb=# select schemaname||'.'||relname as table_name
, last_vacuum
, last_analyze
from pg_stat_user_tables
where schemaname||'.'||relname in ('cds.cdstransaction_rp20_y2021'
 , 'css.image_annotation_rp20_y2021'
 , 'tms.document_rp20_y2021')
order by 1;
   table_name|  last_vacuum  |
last_analyze
-+---+---
 cds.cdstransaction_rp20_y2021   | 2023-12-13 10:42:09.683143-05 |
2023-11-17 04:11:08.761861-05
 css.image_annotation_rp20_y2021 | 2023-09-25 20:00:07.831722-04 |
2023-09-25 20:00:07.831793-04
 tms.document_rp20_y2021 | 2023-12-13 10:42:03.079969-05 |
2023-11-17 04:11:56.583881-05
(3 rows)

Re: Store PDF files in PostgreDB

[Ron Johnson]

On Wed, Dec 6, 2023 at 9:39 AM Priyadharshini Vellaisamy <
priya.cs...@gmail.com> wrote:

> Hi Team,
>
> Please let m know can we store PDF files in PostgreDB ?
>

You can store *anything* up to 1GB in Postgresql using data type bytea.


> If so, can we retrieve it effectively?
>

Effectively?

(We've been storing images in PG for 11 years.)

Max effective number of CPUs that Postgresql can handle?

[Ron Johnson]

PG 9.6.24 on an ESX VM with nproc=32 and RAM=132GB (We'll be on 14.latest
hopefully by February.)

Like the Subject says, is there any point of diminishing returns at which
the Postmaster gets "too busy" to manage all the threads?

(I'm not in control of the stack's architecture, so "change
_insert_topic_here_" -- including connection pooling -- is not in the
cards.)

Thanks

Re: Unable to start postgresql-14

[Ron Johnson]

On Sun, Dec 24, 2023 at 11:04 AM Johnathan Tiamoh 
wrote:

>
> If so how was the backup done?
> It was taken with a customized script that uses pg_dump.
>

That's your problem: pg_dump is a logical backup.  All the WAL records are
now completely invalid.

If you want PITR, read
https://www.postgresql.org/docs/current/continuous-archiving.html *and then
use a dedicated PITR program*.like (alphabetically) barman or pgbackrest.

I like pgbackrest.  It's always worked for me, and the developers respond
quickly to issues raised on github.  Stephen also monitors this list.

Re: vacuumdb did not analyze all tables?=

[Ron Johnson]

On Thu, Dec 14, 2023 at 12:20 PM Francisco Olarte 
wrote:

> Ron:
>
> On Thu, 14 Dec 2023 at 03:39, Ron Johnson  wrote:
> ...
> > Three of the 71 tables were not analyzed.  Why would that be?
> ...
> > vacuumdb -U postgres -h $DbServer --analyze -j6 -t ... -t
> cds.cdstransaction_rp20_y2021 -t ...
> ...
> >  cds.cdstransaction_rp20_y2021   | 2023-12-13 10:42:09.683143-05 |
> 2023-11-17 04:11:08.761861-05
> >  css.image_annotation_rp20_y2021 | 2023-09-25 20:00:07.831722-04 |
> 2023-09-25 20:00:07.831793-04
> >  tms.document_rp20_y2021 | 2023-12-13 10:42:03.079969-05 |
> 2023-11-17 04:11:56.583881-05
>
> I'm not sure if you kept the line, but you have ellipsed-out ( is that
> a word? )


I think so.


> the interesting names, so quoted vacuumdb line is useless
> for check.


71 tables were listed, and didn't want to flood my email with a KB or two
of non-essential text.

I verified that all three tables were in the vacuumdb command line.  (The
list was generated by a query, and stdout and stderr were redirected to a
file, and I grepped it for the table names.)

If you want, I can attach the log file.

Re: vacuumdb did not analyze all tables?=

[Ron Johnson]

On Thu, Dec 14, 2023 at 7:51 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Dec 14, 2023 at 5:46 PM  wrote:
>
>> On Thu, 14 Dec 2023 13:10:16 -0500 Ron Johnson wrote:
>>
>> >> I'm not sure if you kept the line, but you have ellipsed-out ( is that
>> >> a word? )
>>
>> ellipse:  curve
>> ellipsis:  ...
>>
>>
> Though in contect "redacted" makes sense too.
>

Context, not contect.  :D

Re: Assistance Needed: Error during PostgreSQL Configuration

[Ron Johnson]

On Tue, Dec 12, 2023 at 11:02 AM Ayush Vatsa 
wrote:

> Hi,
> Sorry, I should have included the required information initially itself. I
> am new to the database field so please pardon my mistakes
>

Why are you building from source instead of using a packaged solution?
https://www.postgresql.org/download/macosx/

Re: Intermittent Issue with WAL Segment Removal in Logical Replication

[Ron Johnson]

On Thu, Dec 28, 2023 at 4:54 PM Kaushik Iska  wrote:

> Hi all,
>
> I'm including additional details, as I am able to reproduce this issue a
> little more reliably.
>
> Postgres Version: POSTGRES_14_9.R20230830.01_07
> Vendor: Google Cloud SQL
> Logical Replication Protocol version 1
>
> Here are the logs of attempt succeeding right after it fails:
>
> 2023-12-27 01:12:40.581 UTC [59790]: [6-1] db=postgres,user=postgres
> STATEMENT:  START_REPLICATION SLOT peerflow_slot_wal_testing_2 LOGICAL
> 6/5AE67D79 (proto_version '1', publication_names
> 'peerflow_pub_wal_testing_2') <- FAILS
> 2023-12-27 01:12:41.087 UTC [59790]: [7-1] db=postgres,user=postgres
> ERROR:  requested WAL segment 000100060059 has already been
> removed
> 2023-12-27 01:12:44.581 UTC [59794]: [3-1] db=postgres,user=postgres
> STATEMENT:  START_REPLICATION SLOT peerflow_slot_wal_testing_2 LOGICAL
> 6/5AE67D79 (proto_version '1', publication_names
> 'peerflow_pub_wal_testing_2')  <- SUCCEEDS
> 2023-12-27 01:12:44.582 UTC [59794]: [4-1] db=postgres,user=postgres LOG:
>  logical decoding found consistent point at 6/5A31F050
>
> Happy to include any additional details of my setup.
>
> Thanks,
> Kaushik
>
>
> On Tue, Dec 26, 2023 at 10:36 AM Kaushik Iska  wrote:
>
>> Dear PostgreSQL Community,
>>
>> I am seeking guidance regarding a recurring issue we've encountered with
>> WAL segment removal during logical replication using pgoutput plugin. We
>> sporadically encounter an error indicating that a requested WAL segment has
>> already been removed. This issue arises intermittently when executing
>> START_REPLICATION. An example error message is as follows:
>>
>>
>> requested WAL segment 000114600AE has already been removed
>>
>>
>> Please note that this error is not specific to the segment mentioned
>> above; it serves as an example of the type of error we are experiencing.
>>
>> Additional Context:
>>
>>
>>-
>>
>>max_slot_wal_keep_size is -1, logical_decoding_work_mem is 4 GB.
>>-
>>
>>The error seems to appear randomly and is not consistent.
>>-
>>
>>After a couple of retries, the replication process eventually
>>succeeds.
>>-
>>
>>For one of the users it seems to be happening every 16 hours or so.
>>
>>
>> Our approach involves starting with START_REPLICATION 0, replicating data
>> in batches, and then restarting at the last LSN of the previous batch. We
>> are trying to understand the root cause behind the intermittent removal of
>> WAL segments during logical replication. Specifically, we are looking for
>> insights into:
>>
>>
>>-
>>
>>The potential reasons for the WAL segments being reported as removed.
>>-
>>
>>Why this error occurs intermittently and why replication succeeds
>>after several retries.
>>-
>>
>>Any advice on troubleshooting and resolving this issue, or insights
>>into whether it might be related to our specific replication setup or a
>>characteristic of pgoutput, would be highly valuable.
>>
>>
>> Related Posts
>>
>>
>>-
>>
>>https://issues.redhat.com/browse/DBZ-590
>>-
>>
>>Troubleshooting Postgres Sources | Airbyte Documentation
>>
>> 
>>-
>>
>>
>>
>> https://fivetran.com/docs/databases/postgresql/troubleshooting/last-tracked-lsn-error
>>
>>
>>
>> Thank you very much for your time and assistance.
>>
>> Thanks,
>>
>> Kaushik Iska
>>
>>
It might be interesting to see the contents of pg_replication_slots.

Re: Question on overall design

[Ron Johnson]

On Sat, Dec 9, 2023 at 6:14 AM veem v  wrote:
[snip]

> Many of the applications are moving from on premise to AWS cloud as part
> of modernization journey and AWS being chosen cloud partner also the
> product is expected to expand across more regions and this system is
> expected to serve increase in the transaction volume. And also we have a
> requirement to persist transaction data for ~10years to have those
> available for analytics/data science use cases.
>
> So the team is thinking of splitting it into two parts
> 1)OLTP type use case in which we will persist/write the transaction data
> faster and show it to the UI related apps , in near real time/quickest
> possible time. and this database will store Max 60-90 days of transaction
> data. Not sure if we have an option of Oracle exadata equivalent on AWS, so
> team planning of using/experimenting with Aurora postgres. Please correct
> me, if there are any other options we should use otherwise?
>
> 2)Then move the data beyond ~90 days into another database or object
> storage S3 which will keep it there for ~10 years and will be queryable
> using the necessary API's. That is supposed to cater to Olap/analytics/data
> science use cases etc.
>
> Is the above design is okay? and also in regards to the second point above
> i.e. persisting the historical data (that to be in queryable state), should
> we go for some database like snowflake or should just keep it on S3 as is
> and make those queryable through APIs.
>

"OK" is relative, but it's what we did in a similar situation: two years of
data on-line and 5 years of data in compressed files in S3.  (We're
required to keep data for 7 years, but they *never* ask for records more
than 2 years old.  If they ever do, we'll manually load the relevant data
back into PG.)

(I can't imagine that querying billions of unindexed flat-file records via
S3 would be fast.)

How often do end users look for data more than 90 days old?  Two years old?

How quickly does the old data need to be available?

Maybe a four-tiered system of PG-Snowflake-S3-S3_Glacier would balance
speed and cost (or maybe Snowflake would just drive up costs).

Re: Question on overall design

[Ron Johnson]

On Mon, Dec 11, 2023 at 4:41 AM Dominique Devienne 
wrote:

> On Sun, Dec 10, 2023 at 5:56 PM Ron Johnson 
> wrote:
>
>> * We departitioned because SELECT statements were *slow*.  All
>> partitions were scanned, even when the partition key was specified in the
>> WHERE clause.
>>
>
> Surely that's no the case on newer PostgreSQL, is it? Otherwise what's the
> point of partitioning?
> Also, I remember reading something about recent improvements with a large
> number of partitions, no?
>
> As someone who's interested on partitioning, I'd appreciate details.
> Thanks, --DD
>

This was on 12.5.  v13 was just released, and we weren't confident about
running a mission-critical system on a .1 version.

All "transaction" tables were partitioned by month on partion_date, while
the PK was table_name_id, partition_date.

Queries were _slow_, even when the application knew the partion_date range
(since queries might span months).  PG just wouldn't prune.

When I departitioned the tables, performance became acceptable.

Re: Question on overall design

[Ron Johnson]

I don't know anything about Aurora, only have experience with RDS
Postgresql.

We successfully migrated from on-prem Oracle (12c, I think) to RDS
Postgresql 12, and were very happy: little down time (I take pride in
designing/implementing that; thanks, ora2pg!), with lower disk (8TB, down
to 5TB) and CPU usage.

I'm not sure what the TPS was in Oracle, but the server level "we" are on
(I'm no longer with that company, and don't remember the specifics (48 vCPU
/ 132 GB RAM, I think, with 10,000 IOPs) is over-powered for *our* needs.

You're going to have to spin up a full-sized instance, import a *lot* of
real data(*) into a production-identical schema and then run your batch
load process using test data (or copies of real batches).  That's the only
way you'll *really* know.

Beware of excessive partitioning.  We had to "departion" most tables,
because of the impact on performance.

(*) Use ora2pg to export views in the Oracle database.  It's *very* easy; a
small EC2 VM running Linux with enough disk space lets you automate the
extraction from Oracle and importation into AWS Postgresql.)

On Sat, Dec 9, 2023 at 3:36 PM veem v  wrote:

> Thank you so much for the response.
>
> Got your point, will check if we really need details or summary for the
> historical data. But it looks like we will need detailed transaction data
> for ~2 years at least.
>
> My understanding was that AWS has two different offerings and "aurora
> postgresql" is more performant and costlier than "RDS postgresql". Will
> double check on this though.
>
> However , how to prove RDS/aurora postgresql is going to serve our OLTP
> requirement here , similar to the current on premise Oracle exadata. For
> the OLTP use case we are expecting ~15K TPS write and 2K TPS read and the
> response for the UI queries are expected to be within subseconds. But yes,
> as i mentioned individual transactions will be batched and then will be
> written to the database, so this should have lesser resource consumption
> and contention created.
>
> To test if Aurora postgresql will be comparable to cater the above needs
> (in regards to the expected performance with nominal cost) ,how should we
> test it? As we won't be able to test everything right away, Should we test
> basic read and write performance and benchmark to have some confidence and
> go ahead with development?
>
> Say for example if one transaction consists of ~8 Inserts we can create a
> sample target table on aurora Postgre with required indexes/constraints and
> try running those inserts from multiple threads(for concurrency) using
> blazemeter and see/compare the response time, CPU, IO, Memory usage for the
> Postgresql database with set TPS. Similarly to see read performance, we can
> run multiple select queries from blazemeter and compare the response time.
> Is this the correct approach for validating the database here or any other
> approach exists?
>
> And another question coming to mind, I read in past Vaccum to be a problem
> in postgresql, is it going to give trouble in Aurora postgresql too, for
> such a highly transactional read/write system? How to test/validate that?
>
> On Sun, 10 Dec 2023 at 01:29, Ron Johnson  wrote:
>
>> On Sat, Dec 9, 2023 at 2:13 PM veem v  wrote:
>>
>>>
>>> Ron Johnson 
>>>  wrote:
>>>
>>>> "OK" is relative, but it's what we did in a similar situation: two
>>>> years of data on-line and 5 years of data in compressed files in S3.
>>>> (We're required to keep data for 7 years, but they *never* ask for
>>>> records more than 2 years old.  If they ever do, we'll manually load the
>>>> relevant data back into PG.)
>>>> (I can't imagine that querying billions of unindexed flat-file records
>>>> via S3 would be fast.)
>>>> How often do end users look for data more than 90 days old?  Two years
>>>> old?
>>>> How quickly does the old data need to be available?
>>>> Maybe a four-tiered system of PG-Snowflake-S3-S3_Glacier would balance
>>>> speed and cost (or maybe Snowflake would just drive up costs).
>>>
>>>
>>> Thank You so much Ron.
>>>
>>> When you said "*two years of data on-line and 5 years of data in
>>> compressed files in S3*." So do you mean two years of data in aurora
>>> postgre which will be OLTP database and rest just dump as is in S3 bucket
>>> and when we need any such query for those data , just simply read and dump
>>> those S3 files back in the aurora postgre?
>>>
>>
>> (RDS Postgresql, not Aurora, but that's beside the point.)
>>
>> Yes.
>>
>> But keep readin

Re: Question on overall design

[Ron Johnson]

On Sat, Dec 9, 2023 at 2:13 PM veem v  wrote:

>
> Ron Johnson 
>  wrote:
>
>> "OK" is relative, but it's what we did in a similar situation: two years
>> of data on-line and 5 years of data in compressed files in S3.  (We're
>> required to keep data for 7 years, but they *never* ask for records more
>> than 2 years old.  If they ever do, we'll manually load the relevant data
>> back into PG.)
>> (I can't imagine that querying billions of unindexed flat-file records
>> via S3 would be fast.)
>> How often do end users look for data more than 90 days old?  Two years
>> old?
>> How quickly does the old data need to be available?
>> Maybe a four-tiered system of PG-Snowflake-S3-S3_Glacier would balance
>> speed and cost (or maybe Snowflake would just drive up costs).
>
>
> Thank You so much Ron.
>
> When you said "*two years of data on-line and 5 years of data in
> compressed files in S3*." So do you mean two years of data in aurora
> postgre which will be OLTP database and rest just dump as is in S3 bucket
> and when we need any such query for those data , just simply read and dump
> those S3 files back in the aurora postgre?
>

(RDS Postgresql, not Aurora, but that's beside the point.)

Yes.

But keep reading...

In the currently running oracle exadata system , it has SIX months of data
> (which is ~200TB) and the transaction tables are all range partitioned on a
> daily basis. And out of that ~2months of data gets frequently queried and
> other ~4months of data gets queried less frequently.  However, in the
> target architecture which we want to build on cloud here, there are some
> requirements for the analytics/data science team to query ~3years history.
> Beyond ~3years we may need that data rarely.
>
> We were initially thinking of just having one database to serve both OLTP
> and OLAP use cases(somewhat like oracle exadata currently doing for us) but
> it seems we don't have such an option here on AWS. Postgre will serve OLTP
> use case whereas Snowflake will serve OLAP use case.
>
> So do you suggest having both the databases in use, like recent
> transaction data for last 3 months should be streamed to aurora postgre,
> then  from 3months till 3years of data should be parked in snowflake which
> will serve OLAP/analytics use case. and from 3years till 10years will be
> kept in S3 (as parquet or Iceberg format) so that even Snowflake can query
> those directly when needed.
>
> OR
>
> Do you suggest just keeping last ~3months of data on Aurora postgre and
> rest everything on snowflake considering it will store those as compressed
> format and also storage is cheap(23$ per TB per month)?
>
> Few colleagues are pointing to databricks for the analytics use case. Is
> that a good option here?
>
>
I can't answer that without knowing what the end users actually need
(details, or just summaries of historical data, in different tiers).

You all will have to do the cost:benefit analysis of different
architectures.


>
>  On Sat, 9 Dec 2023 at 16:43, veem v  wrote:
>
>> Hello All,
>> Although it's not exactly related to opensource postgre but want to ask
>> this question here to understand colleagues' view, considering  having
>> decades of experience in the database world, We want some guidance, if the
>> below design looks okay for our customer use case.
>>
>> We currently have financial systems transaction data streams to Oracle
>> exadata(X9) on-premise. This database supports processing of 400million
>> transactions per day. A single transaction for us is a combination of 7-8
>> inserts into different transaction tables with Indexes , unique constraints
>> etc defined on those. The transactions processed/committed in batches(~1000
>> batch size) in the database. And this system persists data for ~6 months.
>> We do have all sorts of OLAP(daily/monthly batch reports running)
>> applications run on the same database along with some user facing UI
>> applications showing customer transactions. So it's basically currently
>> serving a hybrid workload and is one stop solution for all use cases.
>>
>> Many of the applications are moving from on premise to AWS cloud as part
>> of modernization journey and AWS being chosen cloud partner also the
>> product is expected to expand across more regions and this system is
>> expected to serve increase in the transaction volume. And also we have a
>> requirement to persist transaction data for ~10years to have those
>> available for analytics/data science use cases.
>>
>> So the team is thinking of splitting it into two parts
>> 1)OLTP type use case in which we will persist/write the

Re: Question on overall design

[Ron Johnson]

On Mon, Dec 11, 2023 at 10:34 PM Chris Travers 
wrote:

> On Tue, Dec 12, 2023 at 2:11 AM Ron Johnson 
> wrote:
>
>> On Mon, Dec 11, 2023 at 4:41 AM Dominique Devienne 
>> wrote:
>>
>>> On Sun, Dec 10, 2023 at 5:56 PM Ron Johnson 
>>> wrote:
>>>
>>>> * We departitioned because SELECT statements were *slow*.  All
>>>> partitions were scanned, even when the partition key was specified in the
>>>> WHERE clause.
>>>>
>>>
>>> Surely that's no the case on newer PostgreSQL, is it? Otherwise what's
>>> the point of partitioning?
>>> Also, I remember reading something about recent improvements with a
>>> large number of partitions, no?
>>>
>>> As someone who's interested on partitioning, I'd appreciate details.
>>> Thanks, --DD
>>>
>>
>> This was on 12.5.  v13 was just released, and we weren't confident about
>> running a mission-critical system on a .1 version.
>>
>
> Something's wrong if all partitions are scanned even when the partition
> clause is explicit in the where clause.
>
> There are however some things which can cause problems here, such as type
> casts of the partition key, or when the partition key is being brought in
> from a join.
>

Here's a snippet.  part_date (type timestamp without time zone) is the
partition key:

  and separation0_.part_date>=to_date('01-Jun-2021', 'DD-Mon-')
  and transmissi1_.part_date>=to_date('01-Jun-2021', 'DD-Mon-')
  and separation0_.part_date=transmissi1_.part_date


>
>> All "transaction" tables were partitioned by month on partion_date, while
>> the PK was table_name_id, partition_date.
>>
>> Queries were _slow_, even when the application knew the partion_date
>> range (since queries might span months).  PG just wouldn't prune.
>>
>
> Was there a datatype issue here?  Like having a partition key of type
> timestamp, but the query casting from date?
>

The partition key was of type timestamp, while "the right hand side of the
predicate".would be whatever to_char() generated.

Re: Import csv to temp table

[Ron Johnson]

On Tue, Jan 2, 2024 at 7:02 AM arun chirappurath 
wrote:

> Dear All,
>
> Do we have any scripts that create a temp table with column names from the
> first row of csv files?
>

How would you determine the data type?

If you assume TEXT for all of them, then it's relatively simple to write
bash which does it.

Re: Question on overall design

[Ron Johnson]

* PG has pgbench; *maybe* you can hack it to work on Oracle.
* If you want to know how well an RDBMS will work on your workload, then
you must provide it with a simulated workload.  Right?
* AWS RDS Postgresql has a dashboard that *might* be similar to AWR.  Or it
might not...
* We departitioned because SELECT statements were *slow*.  All partitions
were scanned, even when the partition key was specified in the WHERE clause.

On Sun, Dec 10, 2023 at 8:45 AM veem v  wrote:

> Thank you so much Ron.  I have some more doubts related to this.
>
> We were thinking , if there is any utility in PG with which we can
> create/generate large sample data volume which we can use to run it on our
> on premise Oracle exadata box and use the same on the aurora postgresql in
> cloud to see the read and write performance comparison. Is there any such
> exists, to quickly get some large data volume?  But i think , what you are
> saying is, we should use real data to get actual or closest possible
> benchmarks, correct me if wrong?
>
> We used to see the data dictionary views (called AWR views) in Oracle to
> see the current and historical performance statistics like CPU, IO , Memory
> usage, object level contentions etc. in the oracle database. Do we have
> such a thing available in Aurora postgre, so as to monitor the
> performance and get some idea of how well the load test goes and what
> capacity is available or are we saturating it?
>
> When you said "*Beware of excessive partitioning.  We had to "departion"
> most tables, because of the impact on performance.*" , as I understand
> partitioning helps in reducing IO while reading , as it scans less data
> because of partition pruning. And while writing there is almost minimal
> overhead in identifying the required partition to which the
> INSERTed/Updated/Deleted data will be landing ON. So can you please help me
> understand what exact performance impact you are highlighting here? Are you
> talking about the size of the partitions or total count of the partitions?
> In our case we are planning to do either daily range partition or hourly
> based on data data volume, not sure if there exists some sweet spot in
> regards to what should be the size of each partition in postgresql be. If
> you are pointing to higher count of partitions of table , then in our case
> if we persist ~90 days data then for a transaction table it would be ~90
> daily range partitions or ~2160 hourly range partitions in the aurora
> postgresql. It would be helpful , if you can explain a bit regarding what
> exact performance impact you faced in regards to the partitioning in aurora
> postgresql.
>
> *"Use ora2pg to export views in the Oracle database. It's very easy; a
> small EC2 VM running Linux with enough disk space lets you automate the
> extraction from Oracle and importation into AWS Postgresql.)"*
>
> Need to explore a bit more on this I believe. We have an oracle on premise
> database, so we can move data directly to aurora postgresql in the cloud.
> Another thing , is we have some sample data available in the AWS snowflake
> but then not sure if some mechanism is available to move the same data to
> the aurora postgresql ?
>
> On Sun, 10 Dec 2023 at 02:27, Ron Johnson  wrote:
>
>> I don't know anything about Aurora, only have experience with RDS
>> Postgresql.
>>
>> We successfully migrated from on-prem Oracle (12c, I think) to RDS
>> Postgresql 12, and were very happy: little down time (I take pride in
>> designing/implementing that; thanks, ora2pg!), with lower disk (8TB, down
>> to 5TB) and CPU usage.
>>
>> I'm not sure what the TPS was in Oracle, but the server level "we" are on
>> (I'm no longer with that company, and don't remember the specifics (48 vCPU
>> / 132 GB RAM, I think, with 10,000 IOPs) is over-powered for *our* needs.
>>
>> You're going to have to spin up a full-sized instance, import a *lot* of
>> real data(*) into a production-identical schema and then run your batch
>> load process using test data (or copies of real batches).  That's the only
>> way you'll *really* know.
>>
>> Beware of excessive partitioning.  We had to "departion" most tables,
>> because of the impact on performance.
>>
>> (*) Use ora2pg to export views in the Oracle database.  It's *very*
>> easy; a small EC2 VM running Linux with enough disk space lets you automate
>> the extraction from Oracle and importation into AWS Postgresql.)
>>
>> On Sat, Dec 9, 2023 at 3:36 PM veem v  wrote:
>>
>>> Thank you so much for the response.
>>>
>>> Got your point, will check if we really need details or summary for the
>>> histo

Re: Postgres 13 streaming replication standby not sending password, 'fe_sendauth: no password supplied'

[Ron Johnson]

On Wed, Jan 10, 2024 at 5:51 PM Keaney, Will  wrote:

> Hello,
>
> I'm building a new 2-node Postgreql 13 streaming replication cluster. I'm
> able to clone the primary to the standby using pg_basebackup.
> However, the standby is unable to authenticate to the primary to begin
> recovery during startup. It logs an error, "FATAL:  could not connect to
> the primary server: fe_sendauth: no password supplied".
>
> I've tried using both .pgpass and passing the password explicitly in the
> myrecovery.conf primary_conninfo string. Debug logs on the primary show the
> initial connection. but no user provided and no authentication attempt.
>
> pg_hba.conf on the primary:
> hostall,replication replprimary-database-server
>scram-sha-256
> hostall,replication replstandby-database-server
>scram-sha-256
>
> myrecovery.conf on the standby:
> primary_conninfo = 'host=primary-database-server port=5432 user=repl
> application_name=standby-server-name'
> recovery_target_timeline = 'latest'
> primary_slot_name = 'standby_replication_slot'
>
> .pgpass on the standby:
> # hostname:port:database:username:password
> *:*:replication:repl:repl_user_password
>
> I've triple-checked that the password in .pgpass matches the password set
> for the repl user in the database, and the repl user has REPLICATION access.
> I'm able to connect to the primary server using the repl user and the psql
> client, both via .pgpass and providing the password directly.
> I can't figure out why the standby postgres server is skipping the
> provided credentials when connecting to the primary.
>
>
Let pg_basebackup do all the work for you:

$ cat ~postgres/.pg_service.conf
[basebackup]
host=
port=5432
user=replicator
passfile=/var/lib/pgsql/.pgpass

$ pg_basebackup \
--pgdata=$PGDATA \
--dbname=service=basebackup \
--verbose --progress \
--checkpoint=fast \
--write-recovery-conf \
--wal-method=stream \
--create-slot --slot=pgstandby1

Re: Refresh Materialized View Issue

[Ron Johnson]

On Thu, Jan 11, 2024 at 3:31 PM Jeremiah Bauer  wrote:

> Hello all,
>
> We are having an issue with a materialized view refresh never finishing,
> any help is appreciated.  It will run at 100% CPU and no IO traffic
> indefinitely after about 15 minutes of parallel workers and the parent
> worker consuming CPU and IO.
>
> PostgreSQL Version: 15.5
>
> Due to some design decisions, we have a large table that we need a
> distinct list of associated id's out of for an application interface.  This
> table has approximately 1,650,000,000 rows with a table size of 175GB and
> 250GB of indexes.
>
> I anonymized the column names in the table structure, but this is the
> table structure of our large table.
>
> create table public.large_table(
>large_table_id bigint NOT NULL DEFAULT
> nextval(public.large_table_id_seq'::regclass),
> nmrc numeric(30,5),
> id1 bigint NOT NULL,
> id2 bigint NOT NULL,
> id3 bigint NOT NULL,
> id4 bigint NOT NULL,
> id5 bigint NOT NULL,
> last_updt_ts timestamp without time zone,
> last_db_updt_ts timestamp without time zone,
> charval character varying(30)
>
> )
>
> The materialized view is defined as:
>
> create materialized view public.vw_distinct_list_of_ids as (
>
> select distinct id1, id2, id3 from public.large_table
>
> ) with no data;
>
> When refreshed the materialized view will contain approximately 59,000,000
> rows.
>
> It takes approximately 12 minutes to run the select statement from the
> view definition.  When you refresh the materialized view for the first
> time, it will spawn several parallel workers that run at 100% CPU and
> produce some I/O for about 12 or 13 minutes, then they finish their work
> and terminate.  The parent worker will continue to run at 100% CPU until I
> terminate the process.  I've let it run for over 24 hours.
>
> What I find interesting is that if I do this:
>
> create table public.table_of_distinct_list_of_ids as (
>
> select distinct id1, id2, id3 from public.large_table
>
> );
>
> It will complete in 12 or 13 minutes, the same as the select statement.  I
> have three questions.
>

My question is: what indexes are on public.large_table?  Hopefully there's
a compound b-tree index on id1, id2, id3.

>
>1. Why is the materialized view refresh stalling and never completing?
>2. Is there a setting I am missing that affects materialized view
>refreshes?
>3. Should I just go with a table that is dropped and recreated
>instead?  I was hoping to avoid this.
>
> A materialized view isn't too different from an unlogged table.

Re: Refresh Materialized View Issue

[Ron Johnson]

On Thu, Jan 11, 2024 at 3:50 PM Jeremiah Bauer  wrote:

> My question is: what indexes are on public.large_table?  Hopefully
> there's a compound b-tree index on id1, id2, id3.
>
> There is not, after further investigation.  There are these 4 indexes that
> involve id1, id2, and id3.  Should I try creating an index on all three of
> the columns?
>
> CREATE INDEX IF NOT EXISTS idx_large_table_id1
>
[snip]

> CREATE INDEX IF NOT EXISTS idx_large_table_id2
>
[snip]

> CREATE INDEX IF NOT EXISTS idx_large_table_id3
>
[snip]

> CREATE INDEX IF NOT EXISTS idx_large_table_id2_id3
>
[snip]

I'd strongly think about creating such an index, since the current indices
don't help much.

Adding id1 to the end of idx_large_table_id2_id3 *might* be a better
option, since it'll reuse much of the existing disk space.


> A materialized view isn't too different from an unlogged table.
>
> So an unlogged table would also be an appropriate solution?
>

Sure, since materialized views are effectively tables with a SELECT
statement bound to it, and are logged.  Thus, unlogged tables are faster to
create.  Of course, being unlogged is a two-edged sword: any unclean
shutdown makes the data go away; you'd have to rebuild the table.

Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?

[Ron Johnson]

*No,* that's a technology problem.  What is the purpose of storing them
back in the database using psql?

On Sat, Jan 13, 2024 at 4:34 PM David Ventimiglia <
davidaventimig...@hasura.io> wrote:

> The business problem I'm trying to solve is:
>
> "How do I capture logical decoding events with the wal2json output
> encoder, filter them with jq, and pipe them to psql, using pg_recvlogical?"
>
>>

Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?

[Ron Johnson]

I think this might be an A-B problem.  Tell us the "business problem" you
are trying to solve, not the problem you're having with your solution to
the "business problem".

(If you've already mentioned it, please restate it.)

On Sat, Jan 13, 2024 at 11:49 AM David Ventimiglia <
davidaventimig...@hasura.io> wrote:

> Thanks.  I'm aware of all of those other alternatives, but the thing is,
> I'm not trying to answer this broader question:
>
> *"What are some options for capturing change events in PostgreSQL?"*
>
> Rather, I'm trying to answer a narrower question:
>
> *"How does one capture output from pg_recvlogical and pipe it back into
> the database with psql?"*
>
> Best,
> David
>
> On Sat, Jan 13, 2024 at 10:29 AM Adrian Klaver 
> wrote:
>
>> On 1/12/24 21:23, David Ventimiglia wrote:
>> > Let me just lay my cards on the table.  What I'm really trying to do is
>> > capture change events with logical decoding and then send them back
>> into
>> > the database into a database table.  To do that, I believe I need to
>> > process the event records into SQL insert statements somehow.  xargs is
>> > one option.  jq is another.  My idea was to pipe the pg_recvlogical
>> > output through a jq transform into psql, but that didn't work (neither
>> > did earlier experiments with xargs).  Redirecting the output to an
>> > intermediate file via stdout was just an attempt to reduce the problem
>> > to a simpler problem.  I had /thought/ (incorrectly, as it turns out)
>> > that I was unable even to redirect it to a file, but evidently that's
>> > not the case.  I can redirect it to a file.  What I cannot seem to do
>> is
>> > run it through a jq filter and pipe it back into psql.  I can run it
>> > through a jq filter and redirect it to a file, no problem.  But the
>> > minute I change it to pipe to psql, it ceases to produce the desired
>> result.
>> >
>> > I tried illustrating this in this screencast:
>> >
>> > https://asciinema.org/a/npzgcTN8DDjUdkaZlVyYJhZ5y
>> > 
>> >
>> > Perhaps another way to put this is, how /does/ one capture output from
>> > pg_recvlogical and pipe it back into the database (or if you like, some
>> > other database) with psql.  When I set out to do this I didn't think
>> > bash pipes and redirection would be the hard part, and yet here I am.
>> > Maybe there's some other way, because I'm fresh out of ideas.
>>
>> This is going to depend a lot on what you define as a change event. Is
>> that DDL changes or data changes or both?
>>
>> Some existing solutions that cover the above to a one degree or another:
>>
>> Event triggers:
>>
>> https://www.postgresql.org/docs/current/event-triggers.html
>>
>> PGAudit
>>
>> https://github.com/pgaudit/pgaudit/blob/master/README.md
>>
>> Or since you are part of the way there already just using logical
>> replication entirely:
>>
>> https://www.postgresql.org/docs/current/logical-replication.html
>>
>>
>> >
>> > Best,
>> > David
>> >
>> > On Fri, Jan 12, 2024 at 8:42 PM Juan Rodrigo Alejandro Burgos Mella
>> > mailto:rodrigoburgosme...@gmail.com>>
>> wrote:
>> >
>> >
>> > try use the following syntax (yes, with a 2 before the greater sign)
>> >
>> > pg_recvlogical -d postgres --slot test --start -f - 2>> sample.jsonl
>> >
>> > Atte
>> > JRBM
>> >
>> > El vie, 12 ene 2024 a las 16:35, David Ventimiglia
>> > (mailto:davidaventimig...@hasura.io
>> >>)
>> > escribió:
>> >
>> > Hello! How do I redirect logical decoding output from the
>> > PostgreSQL CLI tool |pg_recvlogical| either to a file or to
>> > another command via a pipe? I ask because when I try the
>> > obvious, no output is recorded or sent:
>> >
>> > |pg_recvlogical -d postgres --slot test --start -f - >>
>> > sample.jsonl |
>> >
>> > Lest there be any confusion, I already created the slot in an
>> > earlier step. Moreover, I can verify that if I omit the output
>> > redirection |>> sample| then it does work, insofar as it emits
>> > the expected change events when I perform DML in another
>> > terminal window. When I include the redirection (or
>> > alternatively, set up a pipeline), then nothing happens.
>> >
>> > Note that I am aware of the option to pass a filename to the -f
>> > switch to write to a file.  That works, but it's not what I'm
>> > after because it doesn't help update my mental model of how this
>> > is supposed to work.  Based on my current (flawed) mental model
>> > built up from command line experience with other tools, this
>> > /should/ work.  I should be able to send the output to stdout
>> > and then redirect it to a file.  It surprises me that I cannot.
>> >
>> > Anyway, thanks!
>> >
>> > Best,
>> >
>> > David
>> >
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>>

Re: Why scan all columns when we select distinct c1?

[Ron Johnson]

On Sun, Jan 14, 2024 at 6:18 AM Yongtao Huang 
wrote:

> PostgreSQL version: 16.1
> Operating system:   centos7
> Description:
>
> Let me show these explain results first, in PG9.4 and PG16.1.
>
> ### Behavior in PG9.4
> ``` SQL
> gpadmin=# create table t1 (c1 int, c2 text);
> CREATE TABLE
> gpadmin=# explain (costs off, verbose) select distinct c1 from t1;
>  QUERY PLAN
> -
>  HashAggregate
>Output: c1
>Group Key: t1.c1
>->  Seq Scan on public.t1
>  Output: c1 < pay attention <  !!!
> (5 rows)
> ```
>
> ### Behavior in PG 16.1
> ``` SQL
> gpadmin=# create table t1 (c1 int, c2 text);
> CREATE TABLE
> gpadmin=# explain (costs off, verbose) select distinct c1 from t1;
>  QUERY PLAN
> -
>  HashAggregate
>Output: c1
>Group Key: t1.c1
>->  Seq Scan on public.t1
>  Output: c1, c2  < pay attention < !!!
> (5 rows)
> ```
>
> My question is why scan all columns in PG 16.01?
> If `select distinct c1`, scan the column `c1` is enough, like PG 9.4.
>

You can't scan just one column of a row-oriented table.

The real question is why it mentions c2.

Re: How much size saved by updating column to NULL ?

[Ron Johnson]

On Fri, Jan 12, 2024 at 7:45 AM Sébastien TANIERE 
wrote:

> Hello,
> in my company, some columns rarely used in a PG database 14.8 have been
> set to NULL in order to save disk space (datecreation & acteurcreation in
> following table) .
>
> create table example
> (
>  id  varchar(25) not null,
> datecreationtimestamp(6),
> acteurcreation  varchar(50),
> valeurunit  smallint
> )
>
> I am wondering if it is really useful for every type of column.
> Intuitively, i would say that it does not save space for fixed field
> datecreation as it is a fixed size column.
>
> Do we save 8 bytes by timestamp column updated to NULL or not ?
>

I don't think the record on the page is rewritten in a more compact form.
Even if it were, the page would just have a small hole in it.

A different version of this question might be whether the tuple is deleted
and then the hopefully smaller row inserted some where else when
"UPDATE example SET datecreation = NULL;" is executed.  But of course then
you'd use *more* disk space, since now the file(s) will be full of holes.
Vacuum will clean them up so that new data can be inserted there, but the
files would not be shrunk.

Re: Moving to Postgresql database

[Ron Johnson]

On Tue, Jan 16, 2024 at 1:09 PM Adrian Klaver 
wrote:

> On 1/16/24 09:59, Ron Johnson wrote:
>
> > Performance-killing alternatives are not really altternatives.
>
> Unless it is the only one that solves your problem.
>

Amputating one head cures one's migraines, but nobody thinks it's a
viable solution to the problem of migraines.


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

Mimic ALIAS in Postgresql?

[Ron Johnson]

Some RDBMSs have CREATE ALIAS, which allows you to refer to a table by a
different name (while also referring to it by the original name).

We have an application running on DB2/UDB which (for reasons wholly unknown
to me, and probably also to the current developer) extensively uses this
with two schemas: MTUSER and MTQRY.  For example, sometimes refer to
MTUSER.sometable and other times refer to it as MYQRY.sometable.

My goal is to present a way to migrate from UDB to PG with as few
application changes as possible.  Thus, the need to mimic aliases.

Maybe updatable views?
CREATE VIEW mtqry.sometable AS SELECT * FROM mtuser.sometable;

Re: Mimic ALIAS in Postgresql?

[Ron Johnson]

On Tue, Jan 16, 2024 at 12:40 PM Adrian Klaver 
wrote:

> On 1/16/24 09:20, Ron Johnson wrote:
> > Some RDBMSs have CREATE ALIAS, which allows you to refer to a table by a
> > different name (while also referring to it by the original name).
> >
>
> >
> > Maybe updatable views?
> > CREATE VIEW mtqry.sometable AS SELECT * FROM mtuser.sometable;
> >
>
> Assuming sometable is the same name in both schemas then the above will
> not work as:
>
> https://www.postgresql.org/docs/current/sql-createview.html
>
> "The name of the view must be distinct from the name of any other
> relation (table, sequence, index, view, materialized view, or foreign
> table) in the same schema."
>
> You would get a conflict with the existing table MTQRY.sometable.
>

> CREATE VIEW mtqry.sometable AS SELECT * FROM mtuser.sometable;

But mtqry is not the same schema as mtuser..

dba=# create schema mtuser;
CREATE SCHEMA
dba=# create schema mtqry;
CREATE SCHEMA
dba=#
dba=# create table mtuser.sometable(f1 int);
CREATE TABLE
dba=#
dba=# create view mtqry.sometable as select * from mtuser.sometable;
CREATE VIEW

But what are the down-sides that I haven't thought of?

Re: Mimic ALIAS in Postgresql?

[Ron Johnson]

On Tue, Jan 16, 2024 at 2:24 PM Adrian Klaver 
wrote:

>
> On 1/16/24 10:11 AM, Ron Johnson wrote:
>
> On Tue, Jan 16, 2024 at 12:40 PM Adrian Klaver 
> wrote:
>
>> On 1/16/24 09:20, Ron Johnson wrote:
>> > Some RDBMSs have CREATE ALIAS, which allows you to refer to a table by
>> a
>> > different name (while also referring to it by the original name).
>> >
>>
>> >
>> > Maybe updatable views?
>> > CREATE VIEW mtqry.sometable AS SELECT * FROM mtuser.sometable;
>> >
>>
>> Assuming sometable is the same name in both schemas then the above will
>> not work as:
>>
>> https://www.postgresql.org/docs/current/sql-createview.html
>>
>> "The name of the view must be distinct from the name of any other
>> relation (table, sequence, index, view, materialized view, or foreign
>> table) in the same schema."
>>
>> You would get a conflict with the existing table MTQRY.sometable.
>>
>
> > CREATE VIEW mtqry.sometable AS SELECT * FROM mtuser.sometable;
>
> But mtqry is not the same schema as mtuser..
>
> dba=# create schema mtuser;
> CREATE SCHEMA
> dba=# create schema mtqry;
> CREATE SCHEMA
> dba=#
> dba=# create table mtuser.sometable(f1 int);
> CREATE TABLE
> dba=#
> dba=# create view mtqry.sometable as select * from mtuser.sometable;
> CREATE VIEW
>
> But what are the down-sides that I haven't thought of?
>
>
> What happened to the MYQRY schema in your OP?
>
?


> In the above you still have a relation with the same name in different
> schema.
>

Yes.  That's the whole point.


> How does that change the issue?
>
I'm asking how to mimic table aliases, where a table is in MTUSER, but --
via the mechanism of aliases -- can be referenced from schema MTQRY.

Re: postgres sql assistance

[Ron Johnson]

"*invalid input syntax for type boolean: "15"*"

That is the problem.  You can't insert 15 into a column of type "boolean".

On Tue, Jan 16, 2024 at 7:35 AM arun chirappurath 
wrote:

> Dear all,
>
> I am an accidental postgres DBA and learning things every day. Apologies
> for my questions if not properly drafted.
>
> I am trying to load data from the temp table to the main table and catch
> the exceptions inside another table.
>
> temp table is cast with the main table data type and trying to load the
> data.
>
> temp table is below.
>
> category_name  |description
>| is_active
>
> ---+-+---
>  *Tech123212312312323233213123123123123*| Furniture and home decor
>  | true
>  *Tech123212312312323233213123123123123*| Electronic devices and
> accessories  | true
>  Elec| Books of various genres
> | *15*
>  TV  | Books
> | *12*
>  cla | Apparel and fashion accessories
> | true
>
> category name is varchar(25) and is_active is boolean in main table. So i
> should get exceptions for 1st,2nd for category_name rows and 4 and 5th rows
> for boolean. In exception table results,its only showing
>
> Exception table is below. Here instead of showing exception for value 12
> in the is_active table its showing old exception for 15 itself.. Script is
> attached,,...SQLERRM value is not getting updated for row 12..WHat could be
> the reason for this?
>
> value too long for type character varying(25) category_name 1 2024-01-16
> 16:17:01.279 +0530 value too long for type character varying(25)
> description 2 2024-01-16 16:17:01.279 +0530 invalid input syntax for type
> boolean: "15" is_active 3 2024-01-16 16:17:01.279 +0530 *invalid input
> syntax for type boolean: "15" * 4 2024-01-16 16:17:01.279 +0530 *invalid
> input syntax for type boolean: "15"* 5 2024-01-16 16:17:01.279 +0530
>
>

Re: Moving to Postgresql database

[Ron Johnson]

On Tue, Jan 16, 2024 at 12:10 PM Adrian Klaver 
wrote:

> On 1/16/24 09:04, Dominique Devienne wrote:
> > On Tue, Jan 16, 2024 at 5:07 PM Adrian Klaver  > > wrote:
> >
> > On 1/16/24 00:06, Dominique Devienne wrote:
> >  > On Mon, Jan 15, 2024 at 5:17 AM veem v  > 
> >  > >> wrote:
> >  > Is any key design/architectural changes should the app
> > development
> >  > team [...], should really aware about
> >  > Hi. One of the biggest pitfall of PostgreSQL, from the app-dev
> > perspective,
> >  > is the fact any failed statement fails the whole transaction, with
> >  > ROLLBACK as the only recourse.
> >
> > "SAVEPOINT establishes a new savepoint within the current
> transaction.
> >
> >
> > I wish it was that easy.
> > I've been scared away from using them, after reading a few articles...
> > Also, that incurs extra round trips to the server, from the extra
> commands.
>
> The point was that '...  with ROLLBACK as the only recourse.' is not the
> case. There is an alternative, whether you want to use it being a
> separate question.
>

Performance-killing alternatives are not really altternatives.

Re: replication not replicating

[Ron Johnson]

On Tue, Jan 16, 2024 at 4:10 PM Brad White  wrote:

> Errors from the primary
>
> 2024-01-15 00:00:51.157 CST [2660] ERROR:  requested WAL segment
> 0001000200A2 has already been removed
> 2024-01-15 00:00:51.157 CST [2660] STATEMENT:  START_REPLICATION
> 2/A200 TIMELINE 1
> 2024-01-15 00:00:56.158 CST [492] ERROR:  requested WAL segment
> 0001000200A2 has already been removed
> 2024-01-15 00:00:56.158 CST [492] STATEMENT:  START_REPLICATION 2/A200
> TIMELINE 1
> 2024-01-15 00:00:56.159 CST [492] LOG:  could not receive data from
> client: An existing connection was forcibly closed by the remote host.
>
>
> 2024-01-15 00:01:01.151 CST [3404] ERROR:  requested WAL segment
> 0001000200A2 has already been removed
> 2024-01-15 00:01:01.151 CST [3404] STATEMENT:  START_REPLICATION
> 2/A200 TIMELINE 1
> 2024-01-15 00:01:01.152 CST [3404] LOG:  could not receive data from
> client: An existing connection was forcibly closed by the remote host.
>
> [repeat for 55000 lines]
> 
>
> Errors from the hot backup at roughly the same time
>
> 2024-01-15 01:13:57.893 CST [2988] LOG:  started streaming WAL from
> primary at 2/A200 on timeline 1
> 2024-01-15 01:13:57.893 CST [2988] FATAL:  could not receive data from WAL
> stream: ERROR:  requested WAL segment 0001000200A2 has already
> been removed
> 2024-01-15 01:13:57.893 CST [1792] LOG:  waiting for WAL to become
> available at 2/A2002000
> 2024-01-15 01:14:02.884 CST [2552] LOG:  started streaming WAL from
> primary at 2/A200 on timeline 1
> 2024-01-15 01:14:02.884 CST [2552] FATAL:  could not receive data from WAL
> stream: ERROR:  requested WAL segment 0001000200A2 has already
> been removed
> 2024-01-15 01:14:02.884 CST [1792] LOG:  waiting for WAL to become
> available at 2/A2002000
>
> [repeat for 49000 lines]
>

What database version?
Streaming or Logical replication?
Using a slot?

Re: Mimic ALIAS in Postgresql?

[Ron Johnson]

On Tue, Jan 16, 2024 at 5:31 PM Rob Sargent  wrote:

> On 1/16/24 10:20, Ron Johnson wrote:
>
> Some RDBMSs have CREATE ALIAS, which allows you to refer to a table by a
> different name (while also referring to it by the original name).
>
> We have an application running on DB2/UDB which (for reasons wholly
> unknown to me, and probably also to the current developer) extensively uses
> this with two schemas: MTUSER and MTQRY.  For example, sometimes refer to
> MTUSER.sometable and other times refer to it as MYQRY.sometable.
>
> My goal is to present a way to migrate from UDB to PG with as few
> application changes as possible.  Thus, the need to mimic aliases.
>
> Maybe updatable views?
> CREATE VIEW mtqry.sometable AS SELECT * FROM mtuser.sometable;
>
> Isn't it time to get rid of that debt?  A sed -i 's/MTUSER/MTQRY/g' (or
> vice versa) ends what looks to me to be a split brain problem.  All the sql
> is in git right? :)
>
> Or perhaps you have to beef the sed up to use word boundaries just in case.
>

I'm not a Java web developer... 

Re: Mimic ALIAS in Postgresql?

[Ron Johnson]

On Tue, Jan 16, 2024 at 5:57 PM Rob Sargent  wrote:

> On 1/16/24 15:39, Ron Johnson wrote:
>
> On Tue, Jan 16, 2024 at 5:31 PM Rob Sargent  wrote:
>
>> On 1/16/24 10:20, Ron Johnson wrote:
>>
>> Some RDBMSs have CREATE ALIAS, which allows you to refer to a table by a
>> different name (while also referring to it by the original name).
>>
>> We have an application running on DB2/UDB which (for reasons wholly
>> unknown to me, and probably also to the current developer) extensively uses
>> this with two schemas: MTUSER and MTQRY.  For example, sometimes refer to
>> MTUSER.sometable and other times refer to it as MYQRY.sometable.
>>
>> My goal is to present a way to migrate from UDB to PG with as few
>> application changes as possible.  Thus, the need to mimic aliases.
>>
>> Maybe updatable views?
>> CREATE VIEW mtqry.sometable AS SELECT * FROM mtuser.sometable;
>>
>> Isn't it time to get rid of that debt?  A sed -i 's/MTUSER/MTQRY/g' (or
>> vice versa) ends what looks to me to be a split brain problem.  All the sql
>> is in git right? :)
>>
>> Or perhaps you have to beef the sed up to use word boundaries just in
>> case.
>>
>
> I'm not a Java web developer... 
>
>
> You need to adjust you glasses if that's what you see me as.
>

You're the one who apparently sees me as having any control over anything
except when the backups run. 

Re: pg_basebackup Restore problem

[Ron Johnson]

1. What's in $PATH_FOLDER?
2. What pg_basebackup command did you use?
3. Why aren't you letting pg_basebackup maintain the WAL files it needs?

On Wed, Jan 17, 2024 at 11:27 AM Johnathan Tiamoh 
wrote:

> You need to tell us the PG version number *and* show us the full command
> you ran. ?
>
> Postgresql Version 14.10
>
> tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz
>
> ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}
>
> What user are you running pg_basebackup as?
>
> I ran it as postgres and now I'm restoring as postgres
>
> /tnt???  Or /mnt?
> /tnt/backup/current
>
> This is where the backup files are.
>
>
>
>
>
> On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson 
> wrote:
>
>> On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
>> johnathantia...@gmail.com> wrote:
>>
>>> Hello,
>>>
>>> I am trying to restore a pg_basebackup and have the following errors.
>>>
>>>
>> You need to tell us the PG version number *and* show us the full command
>> you ran.
>>
>>
>>> nohup: ignoring input
>>> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>>>
>> tar: /tnt/backup/current/7401.tar.gz: Not found in archive
>>> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
>>> tar: /tnt/backup/current/base.tar.gz: Not found in archive
>>> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
>>> tar: Exiting with failure status due to previous errors
>>> PG_14_202107181/
>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>
>>
>> What user are you running pg_basebackup as?
>>
>>
>>> PG_14_202107181/137502/
>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>> tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
>>> PG_14_202107181/137502/3222926016
>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>
>>> The files exist in the specified directory.
>>>
>>
>> /tnt???  Or /mnt?
>>
>>

Re: Initiate backup from routine?

[Ron Johnson]

On Wed, Jan 17, 2024 at 9:41 AM Troels Arvin  wrote:

> Hello,
>
> I would like to allow a co-worker to perform a backup of a database, such
> that the backup is saved to the database server itself. One use case is
> that (s)he would like an extra backup of a database, just before an
> application update is deployed. The co-worker doesn't have shell access on
> the DB server (so no sudo option), and we would like to allow this to
> happen without having to involve a DBA.
>
> Is it possible to call pg_dump (or equivalent action) through a
> procedure/function?
>
An alternative is continuous (aka PITR) backups using something like
PgBackRest.  Weekly full backups, incremental backups on the other six
days, and WAL files that keep you up to date.

CHECKPOINT; and SELECT pg_switch_wal(); are all that's needed before she
deploys the update.

Re: pg_basebackup Restore problem

[Ron Johnson]

On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh 
wrote:

> Hello,
>
> I am trying to restore a pg_basebackup and have the following errors.
>
>
You need to tell us the PG version number *and* show us the full command
you ran.


> nohup: ignoring input
> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>
tar: /tnt/backup/current/7401.tar.gz: Not found in archive
> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
> tar: /tnt/backup/current/base.tar.gz: Not found in archive
> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
> tar: Exiting with failure status due to previous errors
> PG_14_202107181/
> tar: PG_14_202107181: Cannot mkdir: Permission denied
>

What user are you running pg_basebackup as?


> PG_14_202107181/137502/
> tar: PG_14_202107181: Cannot mkdir: Permission denied
> tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
> PG_14_202107181/137502/3222926016
> tar: PG_14_202107181: Cannot mkdir: Permission denied
>
> The files exist in the specified directory.
>

/tnt???  Or /mnt?

Re: pg_basebackup Restore problem

[Ron Johnson]

Wait a minute... *tar* is throwing the errors, not pg_basebackup, no?

On Wed, Jan 17, 2024 at 3:27 PM Johnathan Tiamoh 
wrote:

> 1. What's in $PATH_FOLDER?
>
> /tnt/backup/current
>
> 2. What pg_basebackup command did you use?
>
> pg_basebackup -D "$baseback_dir" --format=tar   \
> "${comp_opts[@]}" --wal-method=stream --no-password
> --verbose "${PG_DUMP_OPTS[@]}"
>
> /bin/mv "$baseback_dir"/* "/enf/backup/current/"
> /bin/rm -r "$baseback_dir"
>
>
> 3. Why aren't you letting pg_basebackup maintain the WAL files it needs?
>
>
> On Wed, Jan 17, 2024 at 11:46 AM Ron Johnson 
> wrote:
>
>> 1. What's in $PATH_FOLDER?
>> 2. What pg_basebackup command did you use?
>> 3. Why aren't you letting pg_basebackup maintain the WAL files it needs?
>>
>> On Wed, Jan 17, 2024 at 11:27 AM Johnathan Tiamoh <
>> johnathantia...@gmail.com> wrote:
>>
>>> You need to tell us the PG version number *and* show us the full
>>> command you ran. ?
>>>
>>> Postgresql Version 14.10
>>>
>>> tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz
>>>
>>> ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}
>>>
>>> What user are you running pg_basebackup as?
>>>
>>> I ran it as postgres and now I'm restoring as postgres
>>>
>>> /tnt???  Or /mnt?
>>> /tnt/backup/current
>>>
>>> This is where the backup files are.
>>>
>>>
>>>
>>>
>>>
>>> On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson 
>>> wrote:
>>>
>>>> On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
>>>> johnathantia...@gmail.com> wrote:
>>>>
>>>>> Hello,
>>>>>
>>>>> I am trying to restore a pg_basebackup and have the following errors.
>>>>>
>>>>>
>>>> You need to tell us the PG version number *and* show us the full
>>>> command you ran.
>>>>
>>>>
>>>>> nohup: ignoring input
>>>>> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>>>>>
>>>> tar: /tnt/backup/current/7401.tar.gz: Not found in archive
>>>>> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
>>>>> tar: /tnt/backup/current/base.tar.gz: Not found in archive
>>>>> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
>>>>> tar: Exiting with failure status due to previous errors
>>>>> PG_14_202107181/
>>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>>
>>>>
>>>> What user are you running pg_basebackup as?
>>>>
>>>>
>>>>> PG_14_202107181/137502/
>>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>> tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
>>>>> PG_14_202107181/137502/3222926016
>>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>>
>>>>> The files exist in the specified directory.
>>>>>
>>>>
>>>> /tnt???  Or /mnt?
>>>>
>>>>

Re: pg_basebackup Restore problem

[Ron Johnson]

Then you've got a bug somewhere in:
tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz
ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}

On Wed, Jan 17, 2024 at 4:07 PM Johnathan Tiamoh 
wrote:

> Yes.
>
> I am trying to restore the backups on a standby
>
> On Wed, Jan 17, 2024 at 4:04 PM Ron Johnson 
> wrote:
>
>> Wait a minute... *tar* is throwing the errors, not pg_basebackup, no?
>>
>> On Wed, Jan 17, 2024 at 3:27 PM Johnathan Tiamoh <
>> johnathantia...@gmail.com> wrote:
>>
>>> 1. What's in $PATH_FOLDER?
>>>
>>> /tnt/backup/current
>>>
>>> 2. What pg_basebackup command did you use?
>>>
>>> pg_basebackup -D "$baseback_dir" --format=tar   \
>>> "${comp_opts[@]}" --wal-method=stream --no-password
>>> --verbose "${PG_DUMP_OPTS[@]}"
>>>
>>> /bin/mv "$baseback_dir"/* "/enf/backup/current/"
>>> /bin/rm -r "$baseback_dir"
>>>
>>>
>>> 3. Why aren't you letting pg_basebackup maintain the WAL files it needs?
>>>
>>>
>>> On Wed, Jan 17, 2024 at 11:46 AM Ron Johnson 
>>> wrote:
>>>
>>>> 1. What's in $PATH_FOLDER?
>>>> 2. What pg_basebackup command did you use?
>>>> 3. Why aren't you letting pg_basebackup maintain the WAL files it needs?
>>>>
>>>> On Wed, Jan 17, 2024 at 11:27 AM Johnathan Tiamoh <
>>>> johnathantia...@gmail.com> wrote:
>>>>
>>>>> You need to tell us the PG version number *and* show us the full
>>>>> command you ran. ?
>>>>>
>>>>> Postgresql Version 14.10
>>>>>
>>>>> tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz
>>>>>
>>>>> ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}
>>>>>
>>>>> What user are you running pg_basebackup as?
>>>>>
>>>>> I ran it as postgres and now I'm restoring as postgres
>>>>>
>>>>> /tnt???  Or /mnt?
>>>>> /tnt/backup/current
>>>>>
>>>>> This is where the backup files are.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson 
>>>>> wrote:
>>>>>
>>>>>> On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
>>>>>> johnathantia...@gmail.com> wrote:
>>>>>>
>>>>>>> Hello,
>>>>>>>
>>>>>>> I am trying to restore a pg_basebackup and have the following errors.
>>>>>>>
>>>>>>>
>>>>>> You need to tell us the PG version number *and* show us the full
>>>>>> command you ran.
>>>>>>
>>>>>>
>>>>>>> nohup: ignoring input
>>>>>>> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>>>>>>>
>>>>>> tar: /tnt/backup/current/7401.tar.gz: Not found in archive
>>>>>>> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
>>>>>>> tar: /tnt/backup/current/base.tar.gz: Not found in archive
>>>>>>> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
>>>>>>> tar: Exiting with failure status due to previous errors
>>>>>>> PG_14_202107181/
>>>>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>>>>
>>>>>>
>>>>>> What user are you running pg_basebackup as?
>>>>>>
>>>>>>
>>>>>>> PG_14_202107181/137502/
>>>>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>>>> tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
>>>>>>> PG_14_202107181/137502/3222926016
>>>>>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>>>>>
>>>>>>> The files exist in the specified directory.
>>>>>>>
>>>>>>
>>>>>> /tnt???  Or /mnt?
>>>>>>
>>>>>>