Issues with PAM : log that it failed, whether it actually failed or not

Hi, I am having issues with PAM auth : it works, password are correctly checked, unknown users cannot access, known user can, everything looks good But, it always log an error by default even if auth is succesful: 2019-10-10 15:00:46.481 CEST [6109] LOG: pam_authenticate failed: Authenticat

How to make runtime partition pruning work?

I partitioned a table "data_table" by the key "import_id" to reduce the number of partitions to be loaded in my queries. I used list partitions, each containing usually just one "import_id". I used a primary key (id, import_id) But PostgreSQL does not consider partition keys to avoid loading not

Pgbackrest backup is too slow

Hello, I have a Centos 7 server which runs Postgresql 10.7. I am using pgbackrest to take db backup. Problem is backup is too slow. My data dir size is 9.6G and full backup runtime is 22 mins I also tried using process-max=3, full backup runtime = 21 mins Following are the backrest configurations:

Trigger

Can someone please help me in how to list all the triggers with their respective tables and the trigger body

JSON vs. JSONB storage size

I recently stumbled over the presentation "How to Use JSON in MySQL Wrong" by Bill Karwin[1] While most of the indexing part simply doesn't apply to Postgres, I was curious about the statement that the data type of a json value inside the json matters as well (Slide 56) Apparently in MySQL sto

Too many SET TimeZone and Application_name queries

Hi, In our test environment, it was observed that there are too many queries were getting fired to the database server, even though they are not part of the SQL query execution. And the number of queries that were coming to server are very high. Can you please suggest on how to avoid these querie

Re: Trigger

Hello On 2019-10-11 12:59, Sonam Sharma wrote: Can someone please help me in how to list all the triggers with their respective tables and the trigger body You can use the catalogs: SELECT t.tgname, t.tgrelid::regclass, t.tgfoid::regprocedure as function_name, pg_get_functiondef(t.tgfoid) as

Re: Pgbackrest backup is too slow

Greetings, * Ajay Pratap (ajaypra...@ameyo.com) wrote: > I have a Centos 7 server which runs Postgresql 10.7. I am using pgbackrest > to take db backup. > Problem is backup is too slow. Have you tried running 'top' to see what's going on? > My data dir size is 9.6G and full backup runtime is 22

Re: Pgbackrest backup is too slow

Stephen Frost wrote on 2019/10/11 08:50:53 AM: > From: Stephen Frost > To: Ajay Pratap > Cc: Postgres General > Date: 2019/10/11 08:51 AM > Subject: [EXTERNAL] Re: Pgbackrest backup is too slow > > Greetings, > > * Ajay Pratap (ajaypra...@ameyo.com) wrote: > > I have a Centos 7 server which r

Re: Too many SET TimeZone and Application_name queries

On 10/11/19 4:49 AM, Amarendra Konda wrote: Hi, In our test environment, it was observed that there are too many queries were getting fired to the database server, even though they are not part of the SQL query execution. And the number of queries that were coming to server are very high. Ca

Re: Perl DBI converts UTF-8 again to UTF-8 before sending it to the server

Christoph, May I come back to the UTF-8 problem, but now for the reading aspect: I connect to the PG server with: $dbh = DBI->connect($PGDB, $PGDB_USER, $PGDB_PASS, { pg_utf8_flag => 1, pg_enable_utf8 => 1, AutoCommit => 0, RaiseError => 0, PrintError => 0, }

Re: Issues with PAM : log that it failed, whether it actually failed or not

La Cancellera Yoann writes: > I am having issues with PAM auth : > it works, password are correctly checked, unknown users cannot access, > known user can, everything looks good > But, it always log an error by default even if auth is succesful: > And if auth is unsuccessful, it will log that very

Re: JSON vs. JSONB storage size

> On Fri, Oct 11, 2019 at 1:40 PM Thomas Kellerer wrote: > > I am a bit surprised by this (not because the jsonb sizes are generally > bigger, but that the string value takes less space) > > Is this caused by the fact that a string value compresses better internally? Those jsonb objects are quite

Re: JSON vs. JSONB storage size

On 10/11/19 4:40 AM, Thomas Kellerer wrote: I recently stumbled over the presentation "How to Use JSON in MySQL Wrong" by Bill Karwin[1] While most of the indexing part simply doesn't apply to Postgres, I was curious about the statement that the data type of a json value inside the json matter

Re: JSON vs. JSONB storage size

> "Thomas" == Thomas Kellerer writes: Thomas> The table size with jsonb was bigger in general, but the one Thomas> with the "integer" value was even bigger than the one with the Thomas> "string" storage. jsonb stores numeric values as "numeric", not as integers or floats, so the storage n

Re: Pgbackrest backup is too slow

Hello Stephen, Thanks for your prompt response. As recommended by you, I will try to isolate the problem at system level, Meanwhile, Is there any system recommendation by pgbackrest, (how much process-max should be configured) should I set affinity to pgbackrest process, how many core should be kep

Re: Case Insensitive Comparison with Postgres 12

On Fri, Oct 11, 2019 at 1:09 AM stan wrote: > On Thu, Oct 10, 2019 at 05:41:47AM -0700, Igal @ Lucee.org wrote: > > On 10/9/2019 12:34 AM, Laurenz Albe wrote: > > > Igal Sapir wrote: > > > > I am trying to test a simple case insensitive comparison. Most > likely the > > > > collation that I chos

Profile a db connection time?

Sorry if this is silly , but I have series of network outages and wanted to understand if I can profile a psql connection to various parts of it initialling a connection Like when using hostname, resolving dns the time to actually establish a db connection and then the time to parse the query and r

how can I get non-truncated version of running sql?

How do i find the FULL query running on a postgres instance? When I run: SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age FROM pg_stat_activity WHERE state <> 'idle' AND query NOT LIKE '% FROM pg_stat_activity %' ORDER BY age; query seems to be truncated Thanks!

Re: how can I get non-truncated version of running sql?

On 10/11/19 10:23 AM, Julie Nishimura wrote: How do i find the FULL query running on a postgres instance? When I run: SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age FROM pg_stat_activity WHERE state <> 'idle' AND query NOT LIKE '% FROM pg_stat_activity %' ORD

got error: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated

Hi, after a crash i get this errpor: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER:  tuple concurrently updated any idea what i can do? regards walter -- My projects: Admin Boundaries of the World Missing Boundaries

Re: got error: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated

On 10/11/19 10:28 AM, wambac...@posteo.de wrote: Hi, after a crash i get this errpor: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER:  tuple concurrently updated any idea what i can do? More information would be helpful: 1) Postgres version? 2) What is logged just before

Re: got error: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated

On 10/11/19 10:28 AM, wambac...@posteo.de wrote: Hi, after a crash i get this errpor: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER:  tuple concurrently updated any idea what i can do? Additional question: 6) Are there triggers on planet_osm_line? In particular a BEFORE U

Re: got error: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated

Hi Adrin, Am 11.10.19 um 21:42 schrieb Adrian Klaver: On 10/11/19 10:28 AM, wambac...@posteo.de wrote: Hi, after a crash i get this errpor: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER:  tuple concurrently updated any idea what i can do? More information would be helpfu

Re: got error: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated

On 10/11/19 1:56 PM, wambac...@posteo.de wrote: Hi Adrin, Am 11.10.19 um 21:42 schrieb Adrian Klaver: On 10/11/19 10:28 AM, wambac...@posteo.de wrote: Hi, after a crash i get this errpor: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER:  tuple concurrently updated any idea

Re: got error: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated

Hi Adrian, no, system hang and i had to do a power reset (nothing else helped) By system do you mean just Postgres or the computer as a whole? The whole system was hanging (Ubuntu). very strange: did a "lshw" not being root. no idea what was going on. But we don't have to discuss that here.

Re: got error: DELETE FROM planet_osm_line WHERE osm_id = -390840 failed: FEHLER: tuple concurrently updated

On 10/11/19 3:42 PM, wambac...@posteo.de wrote: Hi Adrian, no, system hang and i had to do a power reset (nothing else helped) By system do you mean just Postgres or the computer as a whole? The whole system was hanging (Ubuntu). very strange: did a "lshw" not being root. no idea what was g