Re: initdb when data/ folder has mount points

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

initdb when data/ folder has mount points

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

Re: pg_update to a new machine?

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

pg_update to a new machine?

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"

Re: pg_update to a new machine?

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

Re: pg_update to a new machine?

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

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:

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

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

Re: initdb when data/ folder has mount points

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

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

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

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

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

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

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: 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: Ability to create tables

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 "ABCREAD

Re: pgpass hostname and IP address

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

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

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

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:

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 first

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

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

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

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

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

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

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

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

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

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

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

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

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

Re: Schemas and Search Path

rmance? > > 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

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

Re: unbale to list schema

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

Re: Backup certain months old data

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"

Re: Mimic ALIAS in Postgresql?

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

Re: Question on partitioning

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?

Re: How to do faster DML

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

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

Re: Postgres pg_cron extension

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

Re: PITR

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

Re: How to do faster DML

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

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

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 &l

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

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

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

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

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

('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,

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

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

Re: How to do faster DML

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

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

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

Re: How to do faster DML

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

Re: Encryption Options

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?

Re: Encryption Options

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 >

Re: Encryption Options

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 p

Exclude certain application pgaudit logging?

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

Re: Question on partitioning

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.

Re: Safest pgupgrade jump distance

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

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

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?

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

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

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-out ( i

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?

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

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

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

ting 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/valida

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 data for 7

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

Re: Question on overall design

resql.)"* > > 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 mechanis

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

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

Re: Refresh Materialized View Issue

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

Re: Refresh Materialized View Issue

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

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

*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

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

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 <

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

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 >

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

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

Re: Moving to Postgresql database

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

Mimic ALIAS in Postgresql?

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

Re: Mimic ALIAS in Postgresql?

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

Re: Mimic ALIAS in Postgresql?

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

Re: postgres sql assistance

"*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

Re: Moving to Postgresql database

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

Re: replication not replicating

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 >

Re: Mimic ALIAS in Postgresql?

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 runnin

Re: Mimic ALIAS in Postgresql?

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

Re: pg_basebackup Restore problem

t? > /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: >> >>> Hell

Re: Initiate backup from routine?

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

Re: pg_basebackup Restore problem

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

Re: pg_basebackup Restore problem

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

Re: pg_basebackup Restore problem

t;${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?

  1   2   3   >