pg_dump with compressible and non-compressible tables

Hi, v9.6 We've got big databases where some of the tables are highly compressible, but some have many bytea fields containing PDF files. When the data format is custom, directory or tar, how feasible would a "--no-blob-compression" option be (where pg_dump just tells the zlib library to

Re: Enhancement to psql command, feedback.

On 05/09/2018 02:59 AM, John McKown wrote: I just wanted to throw this out to the users before I made a complete fool of myself by formally requesting it. But I would like what I hope would be a minor change (enhancement) to the psql command. If you look on this page,

Re: Rationale for aversion to the central database?

On 04/27/2018 05:52 PM, g...@luxsci.net wrote: On April 24, 2018 07:27:59 am PDT, "Sam Gendler" wrote: On Sun, Apr 8, 2018 at 15:37 g...@luxsci.net > wrote: On April 8, 2018 02:40:46 pm PDT,

Re: pg_dump with compressible and non-compressible tables

On 05/05/2018 12:13 PM, Adrian Klaver wrote: On 05/05/2018 07:14 AM, Ron wrote: Hi, v9.6 We've got big databases where some of the tables are highly compressible, but some have many bytea fields containing PDF files. Can you see a demonstrable difference? Very much so.  The ASCII hex

Re: [GENERAL] Postgre compatible version with RHEL 7.5

On 05/23/2018 08:13 AM, Adrian Klaver wrote: On 05/23/2018 03:59 AM, Deepti Sharma S wrote: Hi David, “9.6.6 is compatible but not supported”, what does this means? For details see: https://www.postgresql.org/support/versioning/ Basically it is supported by the community, but keeping up

Re: computing z-scores

On 05/24/2018 10:15 AM, Martin Mueller wrote: You construct a z-score for a set of values by subtracting the average from the value and dividing the result by the standard deviation. I know how to do this in a two-step procedure. First, I compute the average and standard deviation. In a

Re: posgresql.log

On 05/21/2018 04:40 PM, Bartosz Dmytrak wrote: Hi Gurus, Looking into my postgresql.log on one of my test servers I found scary entry: --2018-05-19 05:28:21-- http://207.148.79.161/post0514/post Connecting to 207.148.79.161:80... connected. HTTP request sent, awaiting response... 200 OK

Re: Can you make a simple view non-updatable?

On 06/08/2018 04:17 AM, Ryan Murphy wrote: maybe it is time to overhaul the security concept. I could see how I could revoke permissions from, say, all users that aren't superusers to INSERT or UPDATE certain views.  However, if possible it would be nice to get an error message about

Re: Code of Conduct plan

On 06/08/2018 12:09 AM, Gavin Flower wrote: On 08/06/18 16:55, Ron wrote: On 06/07/2018 04:55 AM, Gavin Flower wrote: [snip] The Americans often seem to act as though most people lived in the USA, therefore we should all be bound by what they think is correct! "You" are wea

Re: What does Natvie Posgres mean?

This, to me, is the true meaning of "native PostgreSQL" (as opposed to "stock PostgreSQL", which is uncustomized code).  However, if the job wanted post was written by an HR flunky, it could mean anything. On 06/12/2018 01:11 PM, Benjamin Scherrey wrote: In my experience it refers to

Re: Code of Conduct plan

On 06/07/2018 04:55 AM, Gavin Flower wrote: [snip] The Americans often seem to act as though most people lived in the USA, therefore we should all be bound by what they think is correct! "You" are wearing a tee-shirt (or hoodie), blue jeans and Nikes, while eating a fast food hamburger,

Re: Code of Conduct plan

On 06/03/2018 04:54 PM, Berend Tober wrote: Tom Lane wrote: Two years ago, there was considerable discussion about creating a Code of Conduct for the Postgres community... We are now asking for a final round of community comments... I really like that this was included: "Any allegations that

Re: How to get postmaster shut down time in postgres?

On 06/04/2018 08:44 AM, pavan95 wrote: Hi Adrian/Melvin, Thanks for your prompt replies. Yeah, I'm aware of that way. But my requirement is to get the server shutdown time whenever that event occurs and insert into a table dynamically!! Is it possible? You want to trap the shutdown action

Re: Pgagent is not reading pgpass file either in Windows or Linux.

I've noticed that .pgpass is case sensitive, so am not surprised that it also wouldn't note the difference between 127.0.0.1 and localhost. On 06/04/2018 05:31 PM, nageswara Bandla wrote: I have figured out the issue with pgAgent both in Windows and Linux. PgAgent seems to ignore

Re: Insert UUID GEN 4 Value

On 05/31/2018 07:39 PM, tango ward wrote: On Thu, May 31, 2018 at 12:32 PM, tango ward > wrote: On Thu, May 31, 2018 at 12:18 PM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Wednesday, May 30, 2018, tango ward

Re: Question on disk contention

On 05/31/2018 08:52 AM, Melvin Davidson wrote: On Thu, May 31, 2018 at 1:13 AM, Charles Clavadetscher mailto:clavadetsc...@swisspug.org>> wrote: Hi Melvin As an answer to a previous post you wrote: "Also, your main problem is that when you have two exact same queries

Re: Code of Conduct plan

If there's been so much Bad Behavior that's so Weakened the Community, then someone's done an excellent job of hiding that Bad Behavior. On 06/04/2018 09:57 AM, Evan Macbeth wrote: I just want to chime in and thank all those who worked on this Code of Conduct. It's well thought out, and I'm

Re: CSVQL? CSV SQL? tab-separated table I/O? RENAME COLUMN

On 05/02/2018 04:49 PM, David G. Johnston wrote: [snip] - the microsoft patented CSV would be required for implementation. it handles special data with commas and double-quotes in them ​If true this seems like a show-stopper to anything PostgreSQL would implement If MSFT really

Re: PostgreSQL Volume Question

On 06/15/2018 11:26 AM, Data Ace wrote: Well I think my question is somewhat away from my intention cause of my poor understanding and questioning :( Actually, I have 1TB data and have hardware spec enough to handle this amount of data, but the problem is that it needs too many join

Re: Settings for fast restores

On 08/01/2018 09:11 AM, Vick Khera wrote: On Wed, Aug 1, 2018 at 2:03 AM, Ron <mailto:ronljohnso...@gmail.com>> wrote: Hi, http://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html <http://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html>

Re: Design of a database table

On 07/30/2018 09:37 AM, hmidi slim wrote: I'm trying to design a database table. First of all there are two alternatives: 1-) Divide the table into two tables and make a join. 2-) Design a single table. 1rst alternative: Create table data_periods( id serial primary key not null, period

Settings for fast restores

Hi, http://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html shared_buffers = 1/2 of what you'd usually set maintenance_work_mem = 1GB-2GB wal_level = minimal full_page_writes = off wal_buffers = 64MB checkpoint_segments = 256 or higher max_wal_senders = 0 wal_keep_segments = 0 How

ALTER TABLE .. SET STATISTICS

v9.6.9 For columns of type bytea which store image data (PDFs, JPGs, etc) would it speed up the ANALYZE process to SET STATISTICS = 0? That way, default_statistics_target could be cranked higher -- giving better statistics for needed columns -- without polluting pg_statistics with

Order in which tables are dumped

Hi, v8.4 if it matters. It looked like the tables were being backed up in alphanumeric order, but now I see that table "docformat" is being dumped *after* "doc_image". Are there some other rules besides alphabetical sorting? -- Angular momentum makes the world go 'round.

Re: Order in which tables are dumped

On 07/25/2018 10:28 AM, Tom Lane wrote: Ron writes: It looked like the tables were being backed up in alphanumeric order, but now I see that table "docformat" is being dumped *after* "doc_image". Looks like standard C-locale (ASCII) sort order to me ... I hate spreads

Re: Order in which tables are dumped

On 07/25/2018 10:43 AM, Vick Khera wrote: On Wed, Jul 25, 2018 at 11:15 AM, Ron <mailto:ronljohnso...@gmail.com>> wrote: Hi, v8.4 if it matters. It looked like the tables were being backed up in alphanumeric order, but now I see that table "docformat" is

Re: pg_basebackup failed to read a file

On 08/14/2018 11:14 AM, Tom Lane wrote: Mike Cardwell writes: pg_basebackup: could not get write-ahead log end position from server: ERROR:  could not open file "./postgresql.conf~": Permission denied Now, I know what this error means. There was a root owned file at

Re: Copying data from a CSV file into a table dynamically

On 08/14/2018 08:38 AM, pavan95 wrote: Hi Adrian, I tried to use *"COPY postgres_log1 FROM '/tmp/abc/xyz/postgresql-`date --date="0 days ago" +%Y-%m-%d`_*.csv' WITH csv;"* But it resulted in an error. How to issue such that it is understandable by psql? And I am completely unaware of python &

Re: [External] Multiple COPY on the same table

Maybe he just has a large file that needs to be loaded into a table... On 08/20/2018 11:47 AM, Vijaykumar Jain wrote: Hey Ravi, What is the goal you are trying to achieve here. To make pgdump/restore faster? To make replication faster? To make backup faster ? Also no matter how small you

pg_dump order of operation

Hi, In v8.4, I noticed that the tables seemed to be dumped in alphabetical order.  Not so much, though, in a multithreaded 9.6 dump of an 8.4 database; there's no pattern that I can discern. In what order does the 9.6 pg_dump dump tables? Thanks -- Angular momentum makes the world go

Re: pg_dump order of operation

On 08/26/2018 02:44 PM, Tom Lane wrote: Ron writes: On 08/26/2018 01:42 PM, Tom Lane wrote: Perhaps I don't understand *your* question. What concrete problem are you having? I want to track the progress of pg_dump so as to estimate completion time. Well, if you don't use --jobs then you

Re: pg_dump order of operation

On 08/26/2018 10:24 AM, Tom Lane wrote: Ron writes: In v8.4, I noticed that the tables seemed to be dumped in alphabetical order. Not so much, though, in a multithreaded 9.6 dump of an 8.4 database; there's no pattern that I can discern. In what order does the 9.6 pg_dump dump tables? I

Re: pg_dump order of operation

On 08/26/2018 01:42 PM, Tom Lane wrote: Ron writes: On 08/26/2018 10:24 AM, Tom Lane wrote: Ron writes: In what order does the 9.6 pg_dump dump tables? I don't believe the ordering rules have changed materially since 8.4; it's intended to be by object kind, and within that by name

Improving pg_dump performance

Hi, We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new data center and then restored to v9.6.9. The database has many large tables full of bytea columns containing pdf images, and so the dump file is going to be more than 2x larger than the

Re: Improving pg_dump performance

On 07/23/2018 08:46 AM, Stephen Frost wrote: Greetings, * Ron (ronljohnso...@gmail.com) wrote: An interesting idea.  To clarify: it's possible to parallel backup a running 8.4 cluster remotely from a 9.6 system? Yes, you can do a parallel backup, but you won't be able to get a consistent

Re: Improving pg_dump performance

On 07/23/2018 08:56 AM, Adrian Klaver wrote: On 07/23/2018 06:47 AM, Ron wrote: On 07/23/2018 08:46 AM, Stephen Frost wrote: Greetings, * Ron (ronljohnso...@gmail.com) wrote: An interesting idea.  To clarify: it's possible to parallel backup a running 8.4 cluster remotely from a 9.6

Re: Improving pg_dump performance

On 07/23/2018 09:11 AM, Andres Freund wrote: Hi, On 2018-07-23 02:23:45 -0500, Ron wrote: We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new data center and then restored to v9.6.9. Have you considered using pg_upgrade instead? Yes, but: 1

Re: Improving pg_dump performance

On 07/23/2018 02:32 AM, Andreas Kretschmer wrote: Am 23.07.2018 um 09:23 schrieb Ron: Hi, We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new data center and then restored to v9.6.9. you can use the pg_dump from the newer version (9.6

Re: Improving pg_dump performance

On 07/23/2018 08:27 AM, Andreas Kretschmer wrote: Am 23.07.2018 um 15:06 schrieb Ron: On 07/23/2018 02:32 AM, Andreas Kretschmer wrote: Am 23.07.2018 um 09:23 schrieb Ron: Hi, We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new data

Speccing a remote backup server

Hi, v9.6 backing up v8.4 Where does the gzip run (where the database lives, or the remote server where the pg_dump runs from)?  I ask this because I need to know how beefy to make the backup server.  (It'll just store backups for a version upgrade.) -- Angular momentum makes the world go

Re: Barman versus pgBackRest

On 09/04/2018 10:24 AM, Joshua D. Drake wrote: On 09/04/2018 07:52 AM, Ron wrote: On 09/04/2018 09:24 AM, Joshua D. Drake wrote: On 09/04/2018 07:14 AM, Ron wrote: That was about barman, in the barman group.  This is asking about pgbackrest...  :) So: does pgbackrest have this ability

Re: Max number of WAL files in pg_xlog directory for Postgres 9.2 version

On 09/05/2018 12:39 PM, Raghavendra Rao J S V wrote: Hi All, We are using postgres 9.2 verstion database. Please let me know, how many max number of wal files in pg_xlog directory? What is the formul. I am seeing different formulas. Could you provide me which decides number of max WAL files

Re: PostgreSQL intenal scheduler?

Maybe https://github.com/chanks/que is what you need. On 09/05/2018 02:35 PM, Thiemo Kellner wrote: I have seen pg_cron but it is not what I am looking for. It schedules tasks only by time. I am looking for a fifo queue. pg_cron neither prevents from simultaneous runs I believe. Quoting

pgbackrest when data/base is symlinked to another volume

Hi, Will pgbackrest properly backup and restore the cluster if data/base, data/pg_xlog and data/pg_log are symlinks? PGDATA=/var/lib/pgsql/9.6/data $PGDATA/base -> /Database/9.6/base $PGDATA/pg_log -> /Database/9.6/pg_log $PGDATA/pg_xlog -> /Database/9.6/pg_xlog (I'm not just defining

Volume partitioning (was Re: pgbackrest when data/base is symlinked to another volume)

On 09/08/2018 03:07 PM, David Steele wrote: On 9/7/18 8:47 PM, Ron wrote: On 09/07/2018 05:22 PM, David Steele wrote: On 9/6/18 11:21 PM, Ron wrote: Will pgbackrest properly backup and restore the cluster if data/base, data/pg_xlog and data/pg_log are symlinks? PGDATA=/var/lib/pgsql/9.6

Re: Barman versus pgBackRest

On 09/04/2018 10:51 AM, David Steele wrote: [snip] This will work, but I don't think it's what Ron is getting at. To be clear, it is not possible to restore a database into an *existing* cluster using pgBackRest selective restore. This is a limitation of PostgreSQL file-level backups. To do

Re: Barman versus pgBackRest

On 09/04/2018 09:24 AM, Joshua D. Drake wrote: On 09/04/2018 07:14 AM, Ron wrote: That was about barman, in the barman group.  This is asking about pgbackrest...  :) So: does pgbackrest have this ability which barman does not have? The "--db-include" option seems to indicate th

Re: Barman versus pgBackRest

sept. 2018 à 14:47, Ron <mailto:ronljohnso...@gmail.com>> a écrit : On 09/04/2018 07:14 AM, Thomas Poty wrote: > Do you just change the IP address of the "restore target"? Do you expect a typical restore command? I'm investigating barman and pgBackRest

Re: pgbackrest when data/base is symlinked to another volume

On 09/07/2018 05:22 PM, David Steele wrote: Hi Ron, On 9/6/18 11:21 PM, Ron wrote: Will pgbackrest properly backup and restore the cluster if data/base, data/pg_xlog and data/pg_log are symlinks? PGDATA=/var/lib/pgsql/9.6/data $PGDATA/base -> /Database/9.6/base $PGDATA/pg_log -> /Da

Re: Barman versus pgBackRest

On 09/04/2018 07:14 AM, Thomas Poty wrote: > Do you just change the IP address of the "restore target"? Do you expect a typical restore command? I'm investigating barman and pgBackRest to replace our exitsing NetBackup system, so don't know what you mean by "typical restore command". Here

Re: Barman versus pgBackRest

On 03/09/2018 08:56 AM, David Steele wrote: [snip] About pgBarman, I like : - be able restore on a remote server from the backup server This a good feature, and one that has been requested for pgBackRest. You can do this fairly trivially with ssh, however, so it generally hasn't been a big deal

Re: PG8.3->10 migration data differences

Then fix your field-based data comparing mechanism. On 09/11/2018 03:41 AM, Csaba Ragasits wrote: Hello, We would like to migrate from 8.3 to 10 version. We've hundreds databases with different structures. That reason we're working on an automatic data comparing process. I've found the

Re: Return select statement with sql case statement

On 07/04/2018 10:32 AM, hmidi slim wrote: Actually, I need the use of case because based on the numberOfPremiumDays there are different type of treatment: select numberOfPremiumDays             case  when numberOfPremiumDays = date_part('day', ('2018-11-05'::timestamp) -

Re: Return select statement with sql case statement

On 07/04/2018 05:08 PM, Adrian Klaver wrote: On 07/04/2018 03:03 PM, Ron wrote: On 07/04/2018 10:32 AM, hmidi slim wrote: Actually, I need the use of case because based on the numberOfPremiumDays there are different type of treatment: select numberOfPremiumDays             case  when

Re: Open Source tool to deploy/promote PostgreSQL DDL

On 07/11/2018 03:21 PM, Christopher Browne wrote: I have built one that I call Mahout (https://github.com/cbbrowne/mahout) which has the merit of involving just two shell scripts, one of which is an auditing tool (pgcmp). It implements a "little language" to indicate dependencies between the

Re: Open Source tool to deploy/promote PostgreSQL DDL

On 07/11/2018 03:39 PM, Ravi Krishna wrote: Where I work, the requirement to have rollback scripts is part of the ITIL requirement for Changes to have a backout procedure. Liquibase provides that ability, but IMO rollback for RDBMS is always bit tricky. Certain DDL operations can take long

Re: Open Source tool to deploy/promote PostgreSQL DDL

On 07/11/2018 04:10 PM, Christopher Browne wrote: [snip] ITIL surely does NOT specify the use of database rollback scripts as THE SPECIFIED MECHANISM for a backout procedure. In practice, we tend to take database snapshots using filesystem tools, as that represents a backout procedure that will

Re: Return select statement with sql case statement

On 07/04/2018 07:48 AM, hmidi slim wrote: Hi, I need to use conditional expression in my query, So I want to make a query like this: select numberOfPremiumDays             case  when numberOfPremiumDays = date_part('day', ('2018-11-05'::timestamp) - ('2018-11-01'::timestamp)) then            

Re: Postgresql Split Brain: Which one is latest

You need to find out when the split happened, and whether each new master have records since then. On 04/10/2018 11:47 AM, Vikas Sharma wrote: Thanks Adrian and Edison, I also think so. At the moment I have 2 masters, as soon as slave is promoted to master it starts its own timeline and

Re: pg_basebackup restore a single table

On 04/11/2018 10:21 AM, Andreas Kretschmer wrote: Am 11.04.2018 um 15:53 schrieb camarillo: Can I do a restore of a single table or single base using the archive generated for the basebackup without having to delete the filesystem (/var/lib/pgsql/9.5/*)?. No, but you can use a spare

Re: how to securely delete the storage freed when a table is dropped?

- new pages, or overwrite the existing page? And is any NPI (Non-Public-Info) data in the index itself? * So any PSQL core-engine guys reading? O. On Apr 13, 2018, at 3:03 PM, Ron <ronljohnso...@gmail.com> wrote: On 04/13/2018 12:48 PM, Jonathan Morgan wrote: For a system with infor

Re: pg_dump to a remote server

On 04/16/2018 11:07 PM, Adrian Klaver wrote: On 04/16/2018 06:43 PM, Ron wrote: On 04/16/2018 07:18 PM, Adrian Klaver wrote: On 04/16/2018 04:58 PM, Ron wrote: We're upgrading from v8.4 to 9.6 on a new VM in a different DC.  The dump file will be more than 1TB, and there's not enough disk

Re: Postgresql database encryption

On 04/20/2018 06:11 PM, Stephen Frost wrote: Greetings, * Ron (ronljohnso...@gmail.com) wrote: On 04/20/2018 03:55 PM, Vick Khera wrote: On Fri, Apr 20, 2018 at 11:24 AM, Vikas Sharma <shavi...@gmail.com For anyone to offer a proper solution, you need to say what purpose your encrypt

Re: Postgresql database encryption

On 04/20/2018 03:55 PM, Vick Khera wrote: On Fri, Apr 20, 2018 at 11:24 AM, Vikas Sharma > wrote: Hello Guys, Could someone throw light on the postgresql instance wide or database wide encryption please? Is this possible in postgresql

Re: Postgresql database encryption

On 04/20/2018 10:24 AM, Vikas Sharma wrote: Hello Guys, Could someone throw light on the postgresql instance wide or database wide encryption please? Is this possible in postgresql and been in use in production?. What about encrypted backups? -- Angular momentum makes the world go 'round.

Re: pg_dump to a remote server

On 04/16/2018 07:47 PM, Gao Jack wrote: -Original Message- From: Ron <ronljohnso...@gmail.com> Sent: Tuesday, April 17, 2018 7:59 AM To: pgsql-general <pgsql-gene...@postgresql.org> Subject: pg_dump to a remote server We're upgrading from v8.4 to 9.6 on a new VM in a

A couple of pg_dump questions

$ pg_dump --host=farawaysrvr -Fc $REMOTEDB > /local/disk/backups/$REMOTEDB.dump Is the data compressed on the remote server (thus minimizing traffic on the wire), or locally?  (I'd test this myself, but the company has really strict firewall rules in place.) $ pg_dump --host=farawaysrvr -Fc

Re: Must re-connect to see tables

If it worked in 9.3.22 and now it's failed in 9.3.22, then  I'd look to see if something has changed in data.sql. On 03/27/2018 06:22 AM, Blake McBride wrote: Hi, I have been using PostgreSQL for many years but all of a sudden a db load script I've been using no longer works.  What id does

Re: [EXTERNAL]Re: pg_dump -Fd -j2 on standby in 9.6.6

On 03/28/2018 03:05 AM, Andreas Kretschmer wrote: [snip] > This e-mail message, including any attachments, this is a public mailing list ... The intended recipient is the public mailing list, no? -- Angular momentum makes the world go 'round.

Re: using pg_basebackup for point in time recovery

On 06/21/2018 12:27 AM, Michael Paquier wrote: [snip] Attached is a patch which includes your suggestion. What do you think? As that's an improvement, only HEAD would get that clarification. You've *got* to be kidding. Fixing an ambiguously or poorly worded bit of *documentation* should

Re: COPY from a remote machine in Datastage

On 10/05/2018 09:18 AM, Ravi Krishna wrote: Hello, if you need to use COPY command from remote machine and you use some libpq bindings (aka ruby pg gem for example), you can use functions associated with COPY command (https://www.postgresql.org/docs/10/static/libpq-copy.html). They should

Re: something weird happened - can select by column value although column value exist

On 10/11/2018 03:17 PM, Dmitry O Litvintsev wrote: Hi, Today the following happened: Found this error in my production log: < 2018-10-11 13:31:52.587 CDT >ERROR: insert or update on table "file" violates foreign key constraint "$1" < 2018-10-11 13:31:52.587 CDT >DETAIL: Key

pg_restore to new database wants to wipe out the old database?

Hi, v9.6.9 Why is pg_restore trying to drop my production database, when I (think I) am telling it to create the new database "Molson"? $ cd /backup $ pg_dump -d proddb -j 8 -Fd --no-synchronized-snapshots -Z0 -v -f proddb 2> proddb_pgdump.log $ mv proddb Molson $ pg_restore -vcC

Re: Trouble Upgrading Postgres

On 11/03/2018 02:57 PM, Charles Martin wrote: I'd be grateful for some help. I am trying to move a large database from PostgreSQL 9.6 on Centos 6 to a different server using PostgreSQL 11 on Centos 7. I can't do a pg_dump because it always fails on the largest table. What error message? --

Re: backend crash on DELETE, reproducible locally

On 11/03/2018 02:19 PM, obo...@email.cz wrote: Hello, we reached the exactly same problem after upgrading to PostgreSQL 11 - the server crashed on a DELETE statement with a trigger.We also observed an AFTER DELETE trigger receiving NULL values in OLD. Now I see the problem seems to be solved

Truncated queries in the pg_log file

Hi, v8.4  (we're migrating to 9.6 tomorrow night, but the work must still proceed today.) SQL statements are being logged (which is good), but only the fist 400 or so bytes is recorded.  What knob do I tweak to record the whole query? Here are the relevant postgres.conf items which are not

Re: Truncation of UNLOGGED tables upon restart.

On 11/01/2018 03:58 PM, David G. Johnston wrote: On Thu, Nov 1, 2018 at 1:49 PM Ravi Krishna > wrote: Per documentation unlogged tables are not crash safe and PG will truncate it when it restarts after a crash. Does this apply to even read only unlogged

Re: why select count(*) consumes wal logs

On 11/06/2018 11:12 AM, Michael Nolan wrote: On Tue, Nov 6, 2018 at 11:08 AM Ravi Krishna > wrote: PG 10.5 I loaded 133 million rows to a wide table (more than 100 cols) via COPY. It's always a good idea after doing a large scale data load to do a vacuum

Re: Fwd: Log file

On 11/06/2018 12:06 PM, Igor Korot wrote: [snip] Ok. I guess I will have to write such function. Cron and the relevant log_* config variables should solve your problems. -- Angular momentum makes the world go 'round.

Re: Move cluster to new host, upgraded version

On 11/11/2018 02:51 PM, Rich Shepard wrote: On Sun, 11 Nov 2018, Adrian Klaver wrote: pg_dumpall is going to need to run against a Postgres server not just a data directory. Adrian,   Of course. Yet it's the data directory that's written to the .sql file. Unless your db is small, do a

Re: Move cluster to new host, upgraded version

On 11/12/2018 07:55 AM, Rich Shepard wrote: On Sun, 11 Nov 2018, Ron wrote: Unless your db is small, do a parallel dump. Even then, do a "-Fc" backup instead. That's been the recommended method for many years. Ron,   I've several databases, none 'large.' When I've used

Re: WTF with hash index?

On 11/13/2018 12:07 PM, Andreas Kretschmer wrote: Am 13.11.2018 um 17:42 schrieb Олег Самойлов: insert into gender (gender) select case when random<0.50 then 'female' when random<0.99 then 'male' else 'other' end from (select random() as random, generate_series(1,:table_size)) as subselect;

Re: Running pg_upgrade Version 11

On 11/06/2018 06:30 PM, rob stone wrote: On Tue, 2018-11-06 at 15:17 +0900, Michael Paquier wrote: On Tue, Nov 06, 2018 at 04:27:35PM +1100, rob stone wrote: Logged in as user postgres and postgres owns the files created by initdb, so is this a permissions problem or am I having a brain fade?

CREATE OR REPLACE FUNCTION statement just sitting there

I've got a very puzzling problem on 9.6.6 systems we just migrated from 8.4.  (The same problem happened on 9.6.9, but rolled it back so as to make prod have the same version as our Staging systems.) We've got a giant script full of DROP TRIGGER IF EXISTS and CREATE TABLE and DROP TABLE and

Re: CREATE OR REPLACE FUNCTION statement just sitting there

On 11/05/2018 08:30 PM, Rob Sargent wrote: On 11/5/18 7:05 PM, Ron wrote: I've got a very puzzling problem on 9.6.6 systems we just migrated from 8.4.  (The same problem happened on 9.6.9, but rolled it back so as to make prod have the same version as our Staging systems.) We've got

Re: Idle query that's not ""?

T-ACTIVITY-VIEW "Text of this backend's most recent query. If |state| is |active| this field shows the currently executing query. In all other states, it shows the last query that was executed. " El mar., 6 de nov. de 2018 a la(s) 15:46, Ron (ronljohnso...@gmail.com <mailto:

Re: Idle query that's not ""?

On 11/06/2018 03:04 PM, David G. Johnston wrote: On Tue, Nov 6, 2018 at 1:59 PM Ron wrote: Right. But when does the query text become ""? Or has that become obsolete? (We recently migrated from 8.4.) That behavior changed sometime around 9.0; since it always shows the last quer

Idle query that's not ""?

Hi, v9.6.6 Why do these idle queries (pids 8357, 11260 and 11355) "remember" the queries they ran instead of having the text ""? postgres=# select pid,    xact_start as txn_start,    to_char(EXTRACT(epoch FROM now() - query_start), '999,999.') as query_age_secs,

Re: CREATE OR REPLACE FUNCTION statement just sitting there

On 11/06/2018 05:05 AM, Laurenz Albe wrote: Ron wrote: However, one or more of our big (and schema-identical) prod databases (which are each on a different server) it is finicky and tends to just "sit" at a random one of the CREATE OR REPLACE FUNCTION statements. The "li

Re: CREATE OR REPLACE FUNCTION statement just sitting there

On 11/06/2018 05:34 AM, Alvaro Herrera wrote: On 2018-Nov-05, Ron wrote: That (plus pg_locks)  is the heart of the "list all blocking queries" statement I copied from https://wiki.postgresql.org/wiki/Lock_Monitoring. On that page there's a note about 9.6. Did you see the referen

Re: PgAgent on Windows

On 11/06/2018 07:36 AM, Marcio Meneguzzi wrote: Hello, I´m trying use pgAgent on windows 10 and Windows Server 2012 R2. My version of PostgreSQL is 9.5.1.14 Install and configure pgAgent with sucess, but, when I try run a Job, status is Failed with a message bellow: *"Couldn't create the

Re: pg_dump out of memory for large table with LOB

On 11/14/2018 11:14 AM, Jean-Marc Lessard wrote: Adrien Nayrat wrote: > With 17 million LO, it could eat lot of memory ;) Yes it does. I did several tests and here are my observations. First memory settings are: shared_buffers = 3GB work_mem = 32Mb maintenance_work_mem = 1GB

Re: Trouble Upgrading Postgres

Not enough swap space? On 11/04/2018 04:55 PM, Charles Martin wrote: Yep, you called it: Nov  2 20:30:45 localhost kernel: Out of memory: Kill process 30438 (postmaster) score 709 or sacrifice child Nov  2 20:30:45 localhost kernel: Killed process 30438, UID 26, (postmaster)

Re: How to maintain the csv log files in pg_log directory only for past 30 days

On 09/28/2018 12:03 AM, Raghavendra Rao J S V wrote: Hi All, Log file will be generated in *csv* format at *pg_log* directory in our PostgreSQL. Every day we are getting one log file. We would like to maintain only max 30 days. Which setting need to modify by us in “postgresql.conf” in

Re: Please let me know which configuration setting we need to modify to speedup the pg_dump backup.

On 10/10/2018 09:32 PM, Raghavendra Rao J S V wrote: Hi All, pg_dump is taking more time. Please let me know which configuration setting we need to modify to speedup the pg_dump backup.We are using 9.2 version on Centos Box. Is it taking "more time" because your database is bigger? --

Re: dat names generated by pg_dump

On 09/02/2018 09:26 PM, Tom Lane wrote: Ron writes: I can associate these dat names with their source tables through a bunch of bash and vim manual operations, but I was wondering if there's any automated method (maybe some SQL query of some catalog table; pg_class didn't seem to have

dat names generated by pg_dump

Hi, I can associate these dat names with their source tables through a bunch of bash and vim manual operations, but I was wondering if there's any automated method (maybe some SQL query of some catalog table; pg_class didn't seem to have the relevant data) of making the association. If

Re: dat names generated by pg_dump

On 09/02/2018 08:41 PM, Adrian Klaver wrote: On 09/02/2018 05:40 PM, Ron wrote: Hi, I can associate these dat names with their source tables through a bunch of bash and vim manual operations, but I was wondering if there's any automated method (maybe some SQL query of some catalog table

Re: Implementing standard SQL's DOMAIN constraint [RESOLVED]

On 1/2/19 12:05 PM, Rich Shepard wrote: On Wed, 2 Jan 2019, David G. Johnston wrote: You add the create domain command once before any objects that make use of it. David,   This is the answer I sought: postgres supports the create domain command. I did not see this in your first response.

Re: Dropping and creating a trigger

On 1/5/19 3:59 AM, Mitar wrote: Hi! I am seeing such errors in logs: ERROR: trigger "myapp_assignments" for relation "assignments" already exists STATEMENT: BEGIN TRANSACTION; DROP TRIGGER IF EXISTS "myapp_assignments" ON "assignments"; CREATE TRIGGER "myapp_assignments" AFTER INSERT OR

  1   2   3   4   5   6   7   8   9   10   >