Re: How to write such a query?

On 1/6/22 1:06 AM, Igor Korot wrote: Hi, Ron, On Thu, Jan 6, 2022 at 1:01 AM Ron wrote: On 1/6/22 12:39 AM, Igor Korot wrote: Hi, ALL, In SQLite you can write: SELECT a, b, c FROM foo WHERE id = :id; where ":id" is the named parameter. The query above is similar to SELECT a,b,

Re: WAL Archiving and base backup

On 1/14/22 12:31 PM, Stephen Frost wrote: Greetings, * Issa Gorissen (issa-goris...@usa.net) wrote: Thx a lot. I thought about it but was not so sure about having a complex script (compared to the very simple version when using the exclusive backup - but this this is deprecated...). I will tes

Re: WAL Archiving and base backup

On 1/14/22 1:40 PM, Stephen Frost wrote: [snip] We shouldn't be trying to provide documentation around how to write a tool like pgbackrest, we should, instead, have a tool like pgbackrest in core with its own documentation, as most other RDBMS's do. That's an excellent solution to this problem.

Re: WAL Archiving and base backup

On 1/14/22 3:42 PM, Adrian Klaver wrote: On 1/14/22 13:04, Daniel Westermann (DWE) wrote: On 1/14/22 1:40 PM, Stephen Frost wrote: snip] We shouldn't be trying to provide documentation around how to write a tool like pgbackrest, we should, instead, have a tool like pgbackrest in core with its o

Re: Connecting Postgresql to Google Sheets

On 1/19/22 7:56 AM, Matthias Apitz wrote: El día Mittwoch, Januar 19, 2022 a las 11:24:26 +, Siddharth Golia escribió: Hi, I am trying to get data from PostgreSQL to Google sheets but I am unable to do the same. Google script allows connecting using JDBC Service - https://developers.goog

Re: Can commands be typed in to view geometry in PgAdmin?

pgAdmin, as the name implies, is really for *administering* Postgresql in a GUI manner, *not* for visualizing GIS coordinates. On 1/21/22 10:26 AM, Shaozhong SHI wrote: It would be nice to be able to write lines of scripts to tell PgAdmin to show some visualisation of geographical features. R

Re: pg_cron for vacuum - dynamic table set

On 2/3/22 3:29 PM, saket bansal wrote: Hi, I am trying to schedule pg_cron to generate a set of commands and then run them. E.g |select 'vacuum freeze '||table_name from inventory_for_vacuum \gexec| . This works well at command line, but when scheduled in pg_cron, it fails with syntax error |ER

Re: Re. Backup of postgresql database

On 2/4/22 9:14 PM, sivapostg...@yahoo.com wrote: Hello, Got few (currently 3 will increase as days go) database in A2 hosting unmanaged vps server, running Ubuntu server 18 lts Planning to have an additional backup of all files, including pg database, to another cloud provider like Amazon S3

Re: Re. Backup of postgresql database

ps://go.onelink.me/107872968?pid=InProduct&c=Global_Internal_YGrowth_AndroidEmailSig__AndroidUsers&af_wl=ym&af_sub1=Internal&af_sub2=Global_YGrowth&af_sub3=EmailSignature> On Sat, Feb 5, 2022 at 9:19 AM, Ron wrote: On 2/4/22 9:14 PM, sivapostg...@yahoo.com <mai

Re: How to determine whether I'm running on a standby?

On 2/10/22 1:56 PM, David G. Johnston wrote: On Thu, Feb 10, 2022 at 12:54 PM Mladen Gogala > wrote: The procedure works fine on the primary but reports a bunch of errors on the read-only standbys. A standby is in perpetual recovery mode. (Just like in

Re: Can we go beyond the standard to make Postgres radically better?

On 2/10/22 4:51 PM, Guyren Howe wrote: [snip] I don’t really understand why folks who love the relational model aren’t perpetually up in arms about SQL being their only option. Much better query languages are known and well studied. Because it's Good Enough, and everyone with the wisdom of age

Re: Can we go beyond the standard to make Postgres radically better?

On 2/10/22 10:33 PM, Raymond Brinzer wrote: [snip] Here's one that I think is simple:  why would we want a language where the clauses must come in a particular order?  `FROM mytable SELECT column` is as clear an expression as `SELECT column FROM mytable`, and probably better, in that it starts

Re: 20220221-Clarification regarding PostgeSQL DB backup

On 2/20/22 10:59 PM, Techsupport wrote: Hi Team, ** We have used PG_DUMP to take backup of particular database from the PostgreSQL Server. It takes too long to restore the databases , which has  Half Billion records (almost 8 Hour) My primary need is to make the Differential and Incremental

Re: Enforce primary key on every table during dev?

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 prim

Version upgrade: is restoring the postgres database needed?

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: Version upgrade: is restoring the postgres database needed?

On 03/01/2018 08:46 AM, Melvin Davidson wrote: On Thu, Mar 1, 2018 at 5:24 AM, Ron Johnson <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 databas

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

On 03/01/2018 10:37 AM, Vick Khera wrote: On Thu, Mar 1, 2018 at 11:15 AM, Ron Johnson <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

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

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

Re: Enforce primary key on every table during dev?

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 wo

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

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

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

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

Re: Enforce primary key on every table during dev?

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

Re: Enforce primary key on every table during dev?

On 03/01/2018 12:32 PM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson <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 mailto:rakeshkumar...@aol.co

Re: Enforce primary key on every table during dev?

On 03/01/2018 01:05 PM, Melvin Davidson wrote: On Thu, Mar 1, 2018 at 2:00 PM, Ron Johnson <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

Re: Enforce primary key on every table during dev?

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.

Re: Enforce primary key on every table during dev?

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

Re: Enforce primary key on every table during dev?

On 03/01/2018 02:09 PM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 9:00 PM, Ron Johnson <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 mailto:ron.l.john...@cox.net>> wrote:

Re: Enforce primary key on every table during dev?

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

Re: Enforce primary key on every table during dev?

On 03/01/2018 02:44 PM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 10:32 PM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson mailto:ron.l.john...@cox.net>>wrote: Why have the overhead of a second uniqu

Re: Enforce primary key on every table during dev?

On 03/01/2018 02:32 PM, David G. Johnston wrote: On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson <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 busines

Re: Enforce primary key on every table during dev?

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.wik

Re: Requiring pass and database psql shell command

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

Ability to create tables

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 USA

Re: Ability to create tables

On 03/09/2018 05:46 PM, Tom Lane wrote: Ron Johnson 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";' Tha

pgpass hostname and IP address

(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 w

Re: pgpass hostname and IP address

On 03/09/2018 10:26 PM, David G. Johnston wrote: On Friday, March 9, 2018, Ron Johnson <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 se

Re: pgpass hostname and IP address

On 03/09/2018 11:11 PM, Adrian Klaver wrote: On 03/09/2018 08:19 PM, Ron Johnson 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

psql in a bash function

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 wan

Re: psql in a bash function

al message ---- From: Ron Johnson Date: 3/12/18 2:15 PM (GMT-05:00) To: pgsql-general Subject: psql in a bash function 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).

Reindex doesn't eliminate bloat

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-runni

Re: Reindex doesn't eliminate bloat

On 03/12/2018 05:20 PM, Nikolay Samokhvalov wrote: On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson <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

Re: Reindex doesn't eliminate bloat

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

Re: Reindex doesn't eliminate bloat

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

Re: Reindex doesn't eliminate bloat

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

Re: changing my mail address

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://w

Re: changing my mail address

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

Assigning values to a range in Pgsql and inclusive / exclusive bounds

combinations and I can get it to work using casts and concatenations, e.g. :- * tx = CONCAT('[', 'today'::Timestamptz,',',('now' ::Timestamptz + interval '1 hour'):: timestamptz , ')'):: tstzrange ;* works but I can't h

Re: Assigning values to a range in Pgsql and inclusive / exclusive bounds

Thanks for that perfect... missed the use of tstzrange() as a 'function' in the documentation. Best regards Ron On Fri, 12 Jun 2020 at 21:02, Adrian Klaver wrote: > On 6/12/20 11:45 AM, Ron Clarke wrote: > > Hi, > > > > I've got a simple problem, but

Re: Oracle to PostgreSQL Migration

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 w

Re: Schemas and Search Path

pact 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)

Re: Schema/user/role

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

Re: Can user specification of a column value be required when querying a view ?

Or row level security. On Mon, Nov 20, 2023 at 9:25 PM Alan Hodgson wrote: > On Mon, 2023-11-20 at 13:44 -0800, Christophe Pettus wrote: > > > > On Nov 20, 2023, at 13:41, David Gauthier wrote: > I want the users to be required to provide a value for ssn in the > following query... > "select *

Re: client/server versions

On Tue, Nov 21, 2023 at 6:29 AM Dick Visser wrote: > Hi > > I'm working with AWS RDS PG instances that have been created over time, > and that by now are a mix of several major/minor versions ranging from 12 > to 15. > The initial configuration and management is done from an EC2 instance > runnin

Re: client/server versions

On Tue, Nov 21, 2023 at 8:31 AM Dick Visser wrote: > On Tue, 21 Nov 2023 at 14:07, Ron Johnson wrote: > >> On Tue, Nov 21, 2023 at 6:29 AM Dick Visser wrote: >> >>> Hi >>> >>> I'm working with AWS RDS PG instances that have been created over ti

Re: PITR

On Wed, Nov 22, 2023 at 3:12 AM Rajesh Kumar wrote: > Hi > > A person dropped the table and don't know time of drop. > Revoke his permission to drop tables? > How do I do PITR. Backup strategy is weekly full backup and daily > differential backup. Using pgbackrest. > > Also. In future how do i

Re: Removing oids with pg_repack

On Tue, Nov 21, 2023 at 1:43 PM CG wrote: > I have a very large PostgreSQL 9.5 database that still has very large > tables with oids. I'm trying to get rid of the oids with as little downtime > as possible so I can prep the database for upgrade past PostgreSQL 11. I > had a wild idea to mod pg_re

Re: Feature request: pg_get_tabledef(text)

On Wed, Nov 22, 2023 at 10:41 AM Hans Schou wrote: > Hi > > Similar to pg_get_viewdef() and pg_get_functiondef() it would be useful > with a pg_get_tabledef() to get a full description of how a table is > defined. > Because there's already pg_get_viewdef(), pg_get_functiondef(), pg_get_constrain

Configuration knobs & dials to speed up query optimization

Pg 9.6.24, which will change by April, but not now. We've got some huge (2200 line long) queries that are many UNIONs of complicated queries hitting inheritance-partitioned tables. They can't be refactored immediately, and maybe not at all (complicated applications hitting normalized databases ma

Re: Feature request: pg_get_tabledef(text)

On Wed, Nov 22, 2023 at 11:09 AM Laurenz Albe wrote: > On Wed, 2023-11-22 at 16:41 +0100, Hans Schou wrote: > > Similar to pg_get_viewdef() and pg_get_functiondef() it would be useful > with a pg_get_tabledef() to get a full description of how a table is > defined. > > This has been requested bef

Re: General support on postgres replication

On Wed, Nov 22, 2023 at 11:17 AM Vijaykumar Patil < vijaykumar.pa...@maersk.com> wrote: > Hi Team, > > > > Need some support for below issue . > > > > I have created streaming replication with two nodes . > > > > One is primary and 2nd one is standby but after doing any DML or DDL > operation on p

Re: strange behavior of pg_hba.conf file

On Wed, Nov 22, 2023 at 11:22 AM Atul Kumar wrote: > Hi, > > I have postgres 12 running in centos 7, recently I changed the > authentication of entries of pg_hba.conf to scram-sh-256 for localhost. > > I think you changed something else, at the same time. > Since then I have started getting the

Re: strange behavior of pg_hba.conf file

The error message is EXPLICIT, and DOES NOT LIE. Either someone removed the ::1 entry, or you're now using IPv6. On Wed, Nov 22, 2023 at 12:03 PM Atul Kumar wrote: > The entries that I changed were to replace the md5 with scram-sha-256 and > remove unnecessary remote IPs. > > But it has nothing

Re: pg_restore enhancements

On Wed, Nov 22, 2023 at 2:28 PM Tom Lane wrote: > "Efrain J. Berdecia" writes: > > Thanks, the issue we've run into, which I guess could be really a setup > issue, with running a COPY command while executing pg_restore, is that if > we are restoring a large table (bigger than 500GB) our WAL dire

Re: pg_restore enhancements

On Thu, Nov 23, 2023 at 3:37 AM Laurenz Albe wrote: [snip] > > You can avoidwriting WAL if you set "wal_level = minimal", restart > PostgreSQL > and restore the dump with the --single-transaction option. > Why does "--single-transaction" prevent WAL writes? I'd expect _more_ pg_wal growth from

Re: Configuration knobs & dials to speed up query optimization

On Thu, Nov 23, 2023 at 3:48 AM Laurenz Albe wrote: > On Wed, 2023-11-22 at 11:13 -0500, Ron Johnson wrote: > > Pg 9.6.24, which will change by April, but not now. > > > > We've got some huge (2200 line long) queries that are many UNIONs > of complicated &g

Re: pg_restore enhancements

Thanks for the explanation. On Thu, Nov 23, 2023 at 10:55 AM Tom Lane wrote: > Ron Johnson writes: > > On Thu, Nov 23, 2023 at 3:37 AM Laurenz Albe > > wrote: > >> You can avoidwriting WAL if you set "wal_level = minimal", restart > >> PostgreSQL

Re: Odd Shortcut behaviour in PG14

Out of curiosity, what is the point of adding the "true" predicate no matter the position? Maybe I've created an incorrect truth table, but "true AND" (and "AND true") don't make any logical difference when added to (ekey > 0)*.* On Thu, Nov 23, 2023 at 11:56 AM Zahir Lalani wrote: > Hello all

Re: Odd Shortcut behaviour in PG14

On Fri, Nov 24, 2023 at 8:01 AM Zahir Lalani wrote: > > > > -Original Message- > > From: Tom Lane > > Sent: Thursday, November 23, 2023 7:45 PM > > To: Ron Johnson > > Cc: pgsql-generallists.postgresql.org < > pgsql-general@lists.postgresql.or

Re: replication primary writting infinite number of WAL files

On Fri, Nov 24, 2023 at 11:00 AM Les wrote: [snip] > Writing of WAL files continued after we shut down all clients, and > restarted the primary PostgreSQL server. > > The order was: > > 1. shut down all clients > 2. stop the primary > 3. start the primary > 4. primary started to write like mad ag

Re: Can user specification of a column value be required when querying a view ?

On Fri, Nov 24, 2023 at 1:01 PM Peter J. Holzer wrote: > On 2023-11-20 22:03:06 -0500, Ron Johnson wrote: > > Or row level security. > > Does that help here? AIUI row level security can be used to limit access > to specific rows (e.g. user alex can access info about ssn '

pg_getnameinfo_all() failed: Temporary failure in name resolution

PG 9.6.24 (Yes, I know it's EOL.) I'm seeing lots of these in the postgresql log file: 2023-11-24 15:09:02.224 EST [unknown] [unknown] 18163 [unknown] 01000 WARNING: 01000: pg_getnameinfo_all() failed: Temporary failure in name resolution 2023-11-24 15:09:02.22

Re: pg_getnameinfo_all() failed: Temporary failure in name resolution

On Fri, Nov 24, 2023 at 3:46 PM Adrian Klaver wrote: > On 11/24/23 12:30, Ron Johnson wrote: > > PG 9.6.24 (Yes, I know it's EOL.) > > > > I'm seeing lots of these in the postgresql log file: > > 2023-11-24 15:09:02.224 EST [unknown] [

Re: pg_getnameinfo_all() failed: Temporary failure in name resolution

On Fri, Nov 24, 2023 at 4:26 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Friday, November 24, 2023, Ron Johnson wrote: >> >> >> The second "way" sounds interesting, but what is it filled with? >> > > What does it matter? It’

Re: Can user specification of a column value be required when querying a view ?

On Sat, Nov 25, 2023 at 4:49 AM Peter J. Holzer wrote: > On 2023-11-24 13:06:45 -0500, Ron Johnson wrote: > > On Fri, Nov 24, 2023 at 1:01 PM Peter J. Holzer > wrote: > > On 2023-11-20 22:03:06 -0500, Ron Johnson wrote: > > > Or row level security. > >

Re: pg_getnameinfo_all() failed: Temporary failure in name resolution

On Sun, Nov 26, 2023 at 6:51 AM Peter J. Holzer wrote: [snip] > > But is not explanatory. I want to see host names in the log file when > > possible, not just IP addresses. > > So now that you have IP addresses again, are there any for which a > reverse lookup doesn't work? > I've gone through

Re: PostgreSql: Canceled on conflict out to old pivot

On Mon, Nov 27, 2023 at 2:17 AM Wirch, Eduard wrote: > Hi > > We have a PostgreSql 15 server serving around 30 databases, one schema > each with the same layout. Each database is used by one application > instance. The application consistently uses transactions with isolation > level serializable

Re: Parallel Index Scan Implementation

On Mon, Nov 27, 2023 at 4:11 AM Brajendra Pratap Singh < singh.bpratap...@gmail.com> wrote: > Hi Postgresql Experts, > > We have a basic requirement where we need to implement the parallel index > scan instead of parallel seq scan at table level. Please suggest the best > way to do this. > > Postg

Re: Could not read from file "pg_subtrans/00F5" at offset 122880: Success.

On Wed, Nov 29, 2023 at 12:20 PM Sri Mrudula Attili wrote: > Hello Laurenz, > > > Thanks for your response. > > > This error we are seeing on a delphix Virtual database that was > refreshed using the snapshot of production standalone database. > > > It keeps the database in pg_start_backup and

Re: replication strange behavior

On Thu, Nov 30, 2023 at 3:41 PM Atul Kumar wrote: > Hi, > > I have postgres 12 running in centos 7. > > I have configured streaming replication between one master and one standby > server. > > In the pg_hba.conf file of the master server, I have put the standby > server's hostname instead of IP a

Re: Query related to pg_dump write to a pipe on a windows client and with compressed format

On Fri, Dec 1, 2023 at 9:10 AM Meera Nair wrote: > Hi all, > > > https://www.postgresql.org/message-id/flat/CAKKd065aJ1LuUMw_bhBgmgoM6Ng-cLdBobpzRiU%2BUsdsmW2aOg%40mail.gmail.com#996021734fa788bd1bc737254002ad11 > > We tried pg_dump write to a pipe with compressed format and faced issue as > in t

Re: vacuum visibility relevance

On Sun, Dec 3, 2023 at 9:08 PM senor wrote: > Hi All, > > Simplified Scenario: > 40+ gig table with 20+ indexes receiving log event type records in an > append only fashion. > One of the columns is a sequence ID. > PG version 11.4 > > If I start a vacuum on this table when sequence ID is 1 millio

vacuumdb seems not to like option -j when run from crontab

PG 9.6.24 (Yes, it's EOL.) When running "vacuumdb -p5433 -j4 --analyze tap_d" from a bash prompt, it works as expected: $ vacuumdb -p5433 -j4 --analyze tap_d vacuumdb: vacuuming database "tap_d" But not when running from crontab: vacuumdb -p5433 -j4 --analyze tap_d vacuumdb: invalid option -- 'j'

Re: vacuumdb seems not to like option -j when run from crontab

(Sorry for top-posting. Blame gmail.) Turns out that PG 8.4.20 is also installed from the RHEL repository. Thanks. On Mon, Dec 4, 2023 at 11:13 AM Alan Hodgson wrote: > On Mon, 2023-12-04 at 11:07 -0500, Ron Johnson wrote: > > PG 9.6.24 (Yes, it's EOL.) > > When running

Re: Store PDF files in PostgreDB

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

Max effective number of CPUs that Postgresql can handle?

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

Re: Syntax

On Thu, Dec 7, 2023 at 3:01 AM arun chirappurath wrote: > Hi All, > > What is the difference or use case for below syntaxes? > > do $$ > declare d int; > begin > RAISE INFO 'Script started at %', CURRENT_TIMESTAMP; > update employees set first_name = 'g' where employee_id = 1; get > diagnostics d

Re: Question on overall design

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

Re: Question on overall design

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

Re: Question on overall design

> 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 transa

Re: Question on overall design

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 util

Re: Question on overall design

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 c

Re: Question on overall design

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: >>

Re: Assistance Needed: Error during PostgreSQL Configuration

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/d

vacuumdb did not analyze all tables?=

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.) vac

Re: vacuumdb did not analyze all tables?=

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 > cd

Re: vacuumdb did not analyze all tables?=

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-ou

Re: Postgresql 15 client arm64 and Qt 5.15

On Sun, Dec 17, 2023 at 5:14 PM Piergiorgio Valli wrote: > Hi all, > > I should use the client of Postgresql on Arm64, Host where i build is > Ubuntu 20.04 64bit > > I have downloaded the library from Ubuntu repository > http://ftp.de.debian.org/debian/pool/main/p/postgresql-15/libpq-dev_15.5-0+d

Re: Unable to start postgresql-14

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.postgres

Re: Intermittent Issue with WAL Segment Removal in Logical Replication

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 t

Re: Import csv to temp table

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 whi

<    7   8   9   10   11   12   13   14   15   >