Re: Real application clustering in postgres.
Failover is easy but failback is little bit tricky.I have implemented failback by doing following steps: 1. Start original primary which will be doing crash recovery. It should be designed in such a way that once it is up application should not start connecting to it otherwise there will be split brain and data-mistach between two instances. I implemented it by using a virtual IP mounting on server which is actual primary using keepalived.2. Shutdown original primary and do a pg_rewind to make that as slave for new primary.3. Once slave (original primary) is caught up with primary do failback4. Repeat steps #1-#3 to make failed over instance slave again. Regards,Virendra On Thursday, March 5, 2020, 8:48:54 AM PST, Daulat Ram wrote: Thanks for your inputs Laurenz Albe. Would you please explain single-master failover solution. Suppose we have promoted standby (replica) as master after the h/w issue at Master. If after few hours we recovered the h/w then how we can switchback on the old primary. . As in Oracle we have switchover method for Dataguard. How we can do in Postgres. Thanks, -Original Message- From: Laurenz Albe Sent: Thursday, March 5, 2020 5:37 PM To: Daulat Ram ; pgsql-general@lists.postgresql.org Subject: Re: Real application clustering in postgres. On Thu, 2020-03-05 at 07:45 +, Daulat Ram wrote: > Is there any possibility/options to setup a real application clustering in > Postgres as in Oracle we have a RAC feature. No, and as far as I know nobody feels interested in providing it. RAC is a complicated architecture that doesn't do much good, so most people feel that it would be a waste of time and effort. RAC ist not really a scaling solution: because of the shared storage, you can only scale for more CPUs; I/O remains the bottleneck. RAC is not really a high availability solution: because of the shared storage, it has a sibgle point of failure. Today, people use shared-nothing architectures for high avaliability, like Patroni. > What about multi-master replication in Postgres. would you please suggest how > it is useful and how can setup it. There is no support for that in core PostgreSQL. There is a closed-source implementation that you can buy: https://www.2ndquadrant.com/en/resources/postgres-bdr-2ndquadrant/ But multi-master replication is complicated to get right, and an applicatoin that uses it has to be specifically designed for that. Very often a single-master failover solution is a better solution. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Back Port Request for INVALID Startup Packet
Hi Team, Can you please back port patch where if a 0 byte packet sent to PG instance (Health Checks), it starts complaining about invalid startup packet and flood the log which increases log size considerably if the health checks are every 3 seconds or something like that. Patch Requested - https://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=342cb650e Please let me know if you have already back ported this to supported versions. Regards, Virendra Kumar
Re: Back Port Request for INVALID Startup Packet
Hi Tom, Thank you for your reply! This is simple patch, would that impact badly if patched to prior versions or some other constraints forced to not do that. I am just trying to understand this a bit. On AWS RDS we have primary and secondary hosts known in advance in most cases. So if a primary instance fails over it will be other host and hence we have to update the active nodes in targets using lamda function. AWS RDS fails over very quickly under 3 seconds mostly and hence we have set that health checks to 3seconds. I'll go back to AWS folks and see if they can do this in prior releases. Regards, Virendra Kumar On Wednesday, March 11, 2020, 5:29:38 PM PDT, Tom Lane wrote: Virendra Kumar writes: > Can you please back port patch where if a 0 byte packet sent to PG instance > (Health Checks), it starts complaining about invalid startup packet and flood > the log which increases log size considerably if the health checks are every > 3 seconds or something like that. > Patch Requested - > https://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=342cb650e We generally don't like to change behavior of back branches without ironclad agreement that the existing behavior is a bug ... which this surely isn't. Also, the discussion leading up to that patch specifically considered and rejected back-patching; so I'm disinclined to overrule that decision now. I would suggest that an every-three-second health check is not appropriate, especially one that is so minimal that it only detects whether the postmaster is alive. regards, tom lane
Re: migrate off oracle data to postgres
FDW for oracle and ora2pg both are too slow compared to offload data in a file and load into PostgreSQL. When I was doing one of my migrations I found the best solution was to download data using sqlldr on to the database host where PostgreSQL instance is running and load it using COPY command. Regards, Virendra Kumar On Tuesday, March 31, 2020, 8:55:17 AM PDT, Adrian Klaver wrote: On 3/31/20 8:51 AM, Pepe TD Vo wrote: > I have tables ddl data definitions already. Only need to move the data > over off Oracle to Postgres PostgreSQL Foreign Data Wrapper for Oracle: http://laurenz.github.io/oracle_fdw/ -- Adrian Klaver adrian.kla...@aklaver.com
Different Lock Behavior With Create and Drop Foreign Key
Hi Team, Here is test case.create table testx ( v_code character varying(32), client_id bigint ); alter table testx add constraint testx_pkey primary key (v_code); create table testy ( dxid bigint, v_code character varying(32) ); alter table testy add constraint testy_pkey primary key (dxid);create index on testy (v_code); Let's begin two session, in session1 I am going begin a transaction and run select on parent table: Session1begin;select * from testx; On another session let's call session2, I am running create FK on second table Session2--- alter table testy add constraint testy_fkey foreign key (v_code) references testx(v_code); <--This works.alter table testy drop constraint testy_fkey; <--Hangs Regards,Virendra Kumar
File Foreign Table Doesn't Exist when in Exception
Hello Everyone, I have a weird situation with file_fdw extension when I am creating a foreign table in anonymous block. Here is setup: Create extension and server:== postgres=# create extension file_fdw; CREATE EXTENSION postgres=# CREATE SERVER log_server FOREIGN DATA WRAPPER file_fdw; CREATE SERVER Here is anonymous block, when I query the foreign table (FT) created in block with incorrect data. I get error and the FT is lost. See below: postgres=# DO $$ postgres$# DECLARE postgres$# v_ft_file_name text; postgres$# temp_variable text; postgres$# v_sql text; postgres$# log_min_time date; postgres$# BEGIN postgres$# postgres$# v_ft_file_name:='abc.csv'; postgres$# postgres$# v_sql:= 'CREATE FOREIGN TABLE "'||v_ft_file_name||'"( postgres$# user_name text, postgres$# database_name text, postgres$# connection_from text postgres$# ) SERVER log_server postgres$# OPTIONS (filename ''/opt/postgres/122/data/'||v_ft_file_name||''')'; postgres$# execute v_sql; postgres$# v_sql:='select min(user_name) from "'||v_ft_file_name||'"'; postgres$# execute v_sql into log_min_time; <-- Querying from FT with incorrect data postgres$# postgres$# END; postgres$# $$ LANGUAGE 'plpgsql'; ERROR: missing data for column "database_name" CONTEXT: COPY abc.csv, line 1: "aa,bb,cc" SQL statement "select min(user_name) from "abc.csv"" PL/pgSQL function inline_code_block line 19 at EXECUTE postgres=# postgres=# select ftrelid::regclass::text from pg_foreign_table postgres-# where ftrelid::regclass::text like '%abc.csv%'; ftrelid - (0 rows) When I don't query the FT I can see the foreign table:= postgres=# DO $$postgres$# DECLARE postgres$# v_ft_file_name text; postgres$# temp_variable text; postgres$# v_sql text; postgres$# log_min_time date; postgres$# BEGIN postgres$# postgres$# v_ft_file_name:='abc.csv'; postgres$# postgres$# v_sql:= 'CREATE FOREIGN TABLE "'||v_ft_file_name||'"( postgres$# user_name text, postgres$# database_name text, postgres$# connection_from text postgres$# ) SERVER log_server postgres$# OPTIONS (filename ''/opt/postgres/122/data/'||v_ft_file_name||''')'; postgres$# execute v_sql; postgres$# postgres$# v_sql:='select min(user_name) from "'||v_ft_file_name||'"'; postgres$# --execute v_sql into log_min_time; <-Commented SELECT on FT postgres$# postgres$# END; postgres$# $$ LANGUAGE 'plpgsql'; DO postgres=# postgres=# postgres=# select ftrelid::regclass::text from pg_foreign_table postgres-# where ftrelid::regclass::text like '%abc.csv%'; ftrelid --- "abc.csv" (1 row) postgres=# When I query the table outside anonymous block it is still there. So I am thinking may be I am missing some concept here or hitting a bug: postgres=# select min(user_name) from "abc.csv"; ERROR: missing data for column "database_name" CONTEXT: COPY abc.csv, line 1: "aa,bb,cc" postgres=# postgres=# postgres=# select ftrelid::regclass::text from pg_foreign_table where ftrelid::regclass::text like '%abc.csv%'; ftrelid --- "abc.csv" (1 row) Regards, Virendra Kumar
Re: how to slow down parts of Pg
Autovacuum does takes care of dead tuples and return space to table's allocated size and can be re-used by fresh incoming rows or any updates. Index bloat is still not being taken care of by autovacuum process. You should use pg_repack to do index rebuild. Keep in mind that pg_repack requires double the space of indexes, since there will be two indexes existing during rebuild processes. Regards, Virendra Kumar On Tuesday, April 21, 2020, 2:26:11 PM PDT, Kevin Brannen wrote: From: Michael Loftis >>From: Kevn Brannen >> I don't particularly like doing the vacuum full, but when it will release >> 20-50% of disk space for a large table, then it's something we live with. As >> I understand, a normal vacuum won't release all the old pages that a "full" >> does, hence why we have to do that. It's painful enough I've restricted it >> to once quarter; I'd do it only once a year if I thought I could get away >> with it. Still this is something I'll put on the list to go research with >> practical trials. I don't think the lock for the vacuuming hurts us, but >> I've heard of pg_repack and I'll look into that too. > Why do vacuum full at all? A functional autovacuum will return the free pages > to be reused. You just won’t see the reduction in disk usage at the OS level. > Since the pages are clearly going to be used it doesn’t really make sense to > do a vacuum full at all. Let autovacuum do it’s job or if that’s not keeping > up a normal vacuum without the full. The on dusk sizes will stabilize and > you’ll not be doing a ton of extra I/O to rewrite tables. Sometimes I need the disk space back. It also makes me feel better. (OK, this may not a good reason but there is a hint of truth in this.) What this probably means is that I need to get a better understanding of vacuuming. Thanks! Kevin This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.
Re: how to slow down parts of Pg
;some-phone-' || round(random() * 65000),
postgres-# 'some-phone-' || round(random() * 1000),
postgres-# 'some-phone-' || round(random() * 1000)
postgres-# FROM
postgres-# generate_series(45, 90);
INSERT 0 450001
postgres=# select pg_sleep(120);
pg_sleep
--
(1 row)
postgres=#
postgres=# select
relname,n_tup_ins,n_tup_del,last_autoanalyze,autoanalyze_count from
pg_stat_all_tables where relname ='validate_pg_repack';
relname | n_tup_ins | n_tup_del | last_autoanalyze |
autoanalyze_count
+---+---+---+---
validate_pg_repack | 1350001 | 45 | 2020-04-21 19:37:10.829261-07 |
3
(1 row)
postgres=#
postgres=#
postgres=# select pg_size_pretty(pg_relation_size('validate_pg_repack'));
pg_size_pretty
80 MB
(1 row)
postgres=# \di+ idx_pg_repack_*
List of relations
Schema | Name | Type | Owner | Table
| Size | Description
+--+---+--++---+-
public | idx_pg_repack_effectivedate | index | vvikumar | validate_pg_repack
| 39 MB |
public | idx_pg_repack_masterentityid | index | vvikumar | validate_pg_repack
| 29 MB |
(2 rows)
postgres=# reindex table CONCURRENTLY validate_pg_repack;
REINDEX
postgres=#
postgres=# select pg_size_pretty(pg_relation_size('validate_pg_repack'));
pg_size_pretty
80 MB
(1 row)
postgres=# \di+ idx_pg_repack_*
List of relations
Schema | Name | Type | Owner | Table
| Size | Description
+--+---+--++---+-
public | idx_pg_repack_effectivedate | index | vvikumar | validate_pg_repack
| 19 MB |
public | idx_pg_repack_masterentityid | index | vvikumar | validate_pg_repack
| 19 MB |
(2 rows)
postgres=#
postgres=# drop table validate_pg_repack cascade;
DROP TABLE
postgres=#
postgres=#
postgres=#
Regards,
Virendra Kumar
On Tuesday, April 21, 2020, 3:54:13 PM PDT, Adrian Klaver
wrote:
On 4/21/20 2:32 PM, Virendra Kumar wrote:
> Autovacuum does takes care of dead tuples and return space to table's
> allocated size and can be re-used by fresh incoming rows or any updates.
>
> Index bloat is still not being taken care of by autovacuum process. You
> should use pg_repack to do index rebuild. Keep in mind that pg_repack
> requires double the space of indexes, since there will be two indexes
> existing during rebuild processes.
You sure about that? On Postgres 12:
--2020-04-21 15:47:27.452 PDT-0DEBUG: plant1: vac: 5154 (threshold
1081), anl: 5154 (threshold 565)
--2020-04-21 15:47:27.452 PDT-0DEBUG: autovac_balance_cost(pid=18701
db=25092, rel=26497, dobalance=yes cost_limit=200, cost_limit_base=200,
cost_delay=2)
--2020-04-21 15:47:27.452 PDT-0DEBUG: CommitTransaction(1) name:
unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
--2020-04-21 15:47:27.452 PDT-0DEBUG: StartTransaction(1) name:
unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
--2020-04-21 15:47:27.452 PDT-0DEBUG: vacuuming "public.plant1"
--2020-04-21 15:47:27.504 PDT-0DEBUG: scanned index "p_no_pkey" to
remove 5114 row versions
--2020-04-21 15:47:27.504 PDT-0DETAIL: CPU: user: 0.00 s, system: 0.00
s, elapsed: 0.00 s
--2020-04-21 15:47:27.514 PDT-0DEBUG: scanned index "common_idx" to
remove 5114 row versions
--2020-04-21 15:47:27.514 PDT-0DETAIL: CPU: user: 0.00 s, system: 0.00
s, elapsed: 0.01 s
--2020-04-21 15:47:27.515 PDT-0DEBUG: scanned index "genus_idx" to
remove 5114 row versions
--2020-04-21 15:47:27.515 PDT-0DETAIL: CPU: user: 0.00 s, system: 0.00
s, elapsed: 0.00 s
--2020-04-21 15:47:27.517 PDT-0DEBUG: "plant1": removed 5114 row
versions in 121 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL: CPU: user: 0.00 s, system: 0.00
s, elapsed: 0.00 s
--2020-04-21 15:47:27.517 PDT-0DEBUG: index "p_no_pkey" now contains
5154 row versions in 31 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL: 5114 index row versions were
removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.517 PDT-0DEBUG: index "common_idx" now contains
5154 row versions in 60 pages
--2020-04-21 15:47:27.517 PDT-0DETAIL: 5114 index row versions were
removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
--2020-04-21 15:47:27.517 PDT-0DEBUG: index "genus_idx" now contains
5154 row versions in 47 pages
--2020-04-21 15:
Re: Installing Postgis25_11
I guess it is long route, once I was setting up POSTGIS on one of our cluster. I had to install all of these components by downloading them from their sources. Regards, Virendra On Monday, May 4, 2020, 4:40:53 PM PDT, Clifford Snow wrote: When I try to install PostGIS version 2.5 to a Postgresql-11 installation on Fedora 32 I get warning that nothing provides for these packages: - nothing provides libproj.so.19()(64bit) needed by postgis25_11-2.5.4-1.f32.x86_64 - nothing provides libSFCGAL.so.1()(64bit) needed by postgis25_11-2.5.4-1.f32.x86_64 - nothing provides SFCGAL needed by postgis25_11-2.5.4-1.f32.x86_64 - nothing provides gdal30-libs >= 3.0.4 needed by postgis25_11-2.5.4-1.f32.x86_64 - nothing provides geos38 >= 3.8.1 needed by postgis25_11-2.5.4-1.f32.x86_64 - nothing provides proj70 >= 7.0.0 needed by postgis25_11-2.5.4-1.f32.x86_64 The yum repository [1] doesn't contain packages like these or does Fedora. Any recommendation on how to proceed? Thanks in advance,Clifford -- @osm_washington www.snowandsnow.usOpenStreetMap: Maps with a human touch
Wrong PostgreSQL Plan
Hi Everyone, PG Version - RDS PostgreSQL 10.11 We have very simple query of 3 table joins and a few filter clause. Optimizer is behaving little weird in the sense that for change in one filter it is using NESTED LOOP JOIN and running for over 30 seconds whereas for other filter is working fine using HASH JOIN and under mili seconds. Here are two plans: Query: SELECT COALESCE(TicketEXT,0) FROM COSTMAX C, UNITMAX U, UNITTYP T WHERE C.UCE=U.UCE AND C.MADESC=T.MADESC AND C.STC=T.STC AND C.PTC=T.PTC AND C.MADESC='LAX' AND C.RD='5May2020' AND upper(T.STYPE)='DA' AND upper(T.TYPE)='ACT' AND upper(U.UNAME)='I' Good PlanHash Join (cost=193.18..1653.63 rows=1 width=32) Hash Cond: ((u.uce)::numeric = c.uce) -> Seq Scan on unitmax u (cost=0.00..1457.67 rows=185 width=4) Filter: (upper(uname) = 'I'::text) -> Hash (cost=192.79..192.79 rows=31 width=11) -> Nested Loop (cost=0.70..192.79 rows=31 width=11) -> Index Scan using pk_styppe on unittyp t (cost=0.14..2.47 rows=1 width=15) Index Cond: (madesc = 'LAX'::text) Filter: ((upper(stype) = 'DA'::text) AND (upper(type) = 'ACT'::text)) -> Index Scan using costmax_pk on costmax c (cost=0.56..189.85 rows=47 width=25) Index Cond: (((madesc)::text = 'LAX'::text) AND ((stc)::text = t.stc) AND ((ptc)::text = t.ptc) AND (rd = '2020-04-27'::date)) Bad PlanNested Loop (cost=0.70..1619.45 rows=1 width=32) Join Filter: (c.uce = (u.uce)::numeric) -> Nested Loop (cost=0.70..159.01 rows=1 width=11) -> Index Scan using pk_styppe on unittyp t (cost=0.14..2.47 rows=1 width=15) Index Cond: (madesc = 'LAX'::text) Filter: ((upper(stype) = 'DA'::text) AND (upper(type) = 'ACT'::text)) -> Index Scan using costmax_pk on costmax c (cost=0.56..156.52 rows=1 width=25) Index Cond: (((madesc)::text = 'LAX'::text) AND ((stc)::text = t.stc) AND ((ptc)::text = t.ptc) AND (rd = '2020-05-01'::date)) -> Seq Scan on unitmax u (cost=0.00..1457.67 rows=185 width=4) Filter: (upper(uname) = 'I'::text) We have played little bit around default_statistics_target, sometimes it worked when the setting is around 1500 other times it doesn't work even with setting as high as 5000. Is there anything community can suggest us in resolving this? Regards, Virendra
Re: Wrong PostgreSQL Plan
Hi David, Thank you for your reply! You mean when you change the date equality filter to a more recent date? - This is correct we have faced this when we change equality filter to more recent date. Will be sending explain analyze on the thread when I get that. We ran analyze many time to see if that resolves but looks like it is not buzzing. Also for regular analyze we don't run manually, they are all left to autoanalyze process. Regards,Virendra On Tuesday, May 5, 2020, 6:34:37 PM PDT, David G. Johnston wrote: On Tue, May 5, 2020 at 6:15 PM Virendra Kumar wrote: Optimizer is behaving little weird in the sense that for change in one filter You mean when you change the date equality filter to a more recent date? it is using NESTED LOOP JOIN and running for over 30 seconds whereas for other filter is working fine using HASH JOIN and under mili seconds. Which means it runs to completion which means EXPLAIN ANALYZE is possible and considerably more helpful. We have played little bit around default_statistics_target, sometimes it worked when the setting is around 1500 other times it doesn't work even with setting as high as 5000. Is there anything community can suggest us in resolving this? Given that adding more data causes the good plan to stop working I would have to say that increasing how often you run ANALYZE on the table, irrespective of default_statistics_target, is probably a good thing to try. David J.
Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
Here is my thought on why row is not limiting when joined vs why it is limiting when not joined. When not joined and where clause is having IN, it is using index process_activity_process_instance_id_app_id_created_idx which has columns process_instance_id, created which is in order by and hence no additional ordering is required and a direct rows limit can be applied here. When in join condition it has to fetch rows according to filter clause, join them and then order ( sort node in plan) hence it cannot limit rows while fetching it first time from the table. You are also missing pi.user_id = '317079413683604' in exists clause. It is worth trying to put there and run explain again and see where it takes. But to your point row limitation cannot happen in case of join as such in the query. Regards, Virendra On Thursday, May 7, 2020, 11:52:00 AM PDT, Amarendra Konda wrote: Hi Virendra, Thanks for your time. Here is the table and index structure process_activity Table "public.process_activity" Column | Type | Modifiers +-+ process_activity_id | bigint | not null default next_id() process_activity_type | smallint | not null voice_url | text | process_activity_user_id | bigint | not null app_id | bigint | not null process_instance_id | bigint | not null alias | text | not null read_by_user | smallint | default 0 source | smallint | default 0 label_category_id | bigint | label_id | bigint | csat_response_id | bigint | process_activity_fragments | jsonb | created | timestamp without time zone | not null updated | timestamp without time zone | rule_id | bigint | marketing_reply_id | bigint | delivered_at | timestamp without time zone | reply_fragments | jsonb | status_fragment | jsonb | internal_meta | jsonb | interaction_id | text | do_not_translate | boolean | should_translate | integer | in_reply_to | jsonb | Indexes: "process_activity_pkey" PRIMARY KEY, btree (process_activity_id) "fki_process_activity_konotor_user_user_id" btree (process_activity_user_id) WITH (fillfactor='70') "process_activity_process_instance_id_app_id_created_idx" btree (process_instance_id, app_id, created) WITH (fillfactor='70') "process_activity_process_instance_id_app_id_read_by_user_created_idx" btree (process_instance_id, app_id, read_by_user, created) WITH (fillfactor='70') "process_activity_process_instance_id_idx" btree (process_instance_id) WITH (fillfactor='70') process_instance Table "public.process_instance" Column | Type | Modifiers -+-+- process_instance_id | bigint | not null default next_id() process_instance_alias | text | not null app_id | bigint | not null user_id | bigint | not null Indexes: "process_instance_pkey" PRIMARY KEY, btree (process_instance_id) "fki_conv_konotor_user_user_id" btree (user_id) WITH (fillfactor='70') Regards, Amarendra On Fri, May 8, 2020 at 12:01 AM Virendra Kumar wrote: Sending table structure with indexes might help little further in understanding. Regards, Virendra On Thursday, May 7, 2020, 11:08:14 AM PDT, Amarendra Konda wrote: Hi David, In earlier reply, Over looked another condition, hence please ignore that one Here is the correct one with all the needed conditions. According to the latest one, exists also not limiting rows from the process_activity table. EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id AS pa_process_activity_id FROM process_activity pa WHERE pa.app_id = '126502930200650' AND pa.created > '1970-01-01 00:00:00' AND EXISTS ( SELECT 1 FROM process_instance pi
Re: vacuumdb --jobs deadlock: how to avoid pg_catalog ?
You might have index fragmentation and possibly reindexing them conncurrently
on PG12, should do that. As everyone agreed most of space will be marked for
re-use later for table segments but indices in your case could be problem. On
previous versions you can use pg_repack to do index rebuilds which will help
reduce space consumed.
Regards,
Virendra
On Thursday, May 14, 2020, 1:20:41 PM PDT, Eduard Rozenberg
wrote:
@Adrian thanks again.
I read the postgres docs the same way - that previously used space is marked as
available and therefore no need for vacuum full. Previously used = now
available space, which gets re-used, in theory.
And yet this same DB is growing at 30-50 GB weekly, despite the fact that 2.4
TB of previously used space is clearly available ("clearly available" as proven
by vacuum full shrinking the DB space usage by 2.4 TB). I did verify
postgresql.conf has always been properly configured re: autovacuum:
'autovacuum = on'and 'track_counts = on'
I'm not planning on running VACUUM FULL regularly, just "this one time". And I
was trying to to parallelize VACUUM FULL and minimize downtime of the
production DB caused by table locking. And then I found the option of using
"vacuumdb --jobs" which sounded like the perfect solution except for "well you
can't actually use --jobs because you'll run into a deadlock and everybody
knows that and nobody has a (good) solution for it" :).
--Ed
> On May 14, 2020, at 11:46, Adrian Klaver wrote:
>
> On 5/14/20 11:27 AM, Eduard Rozenberg wrote:
>> @Adrian thanks.
>> I did a test vacuum full the "normal" non-parallel way (VACUUM FULL sql)
>> previously on a test db copy and saw the DB size (postgres 9.6) shrink from
>> 6.8 TB to 4.4 TB of actual, real disk space usage ($ df -d 1).
>> I don't know the reason so much space was "locked up" (other than there is a
>> lot of data "churn" from daily updates). But I definitely do need to do the
>> vac full on the production db to get down to the smaller size - cannot
>> afford the 2.4 TB of "wasted" space on an ongoing basis.
>
> It may not be wasted space. A regular VACUUM marks space within a table
> available for reuse(and not returned to OS) when it removes unneeded tuples.
> It then fills that space up with new tuples, roughly speaking. So if the
> vacuuming is working properly you will reach a steady state where space is
> reused and the database on disk size grows slowly as reusable space is
> occupied. I would monitor the database size on a regular basis. My guess is
> that the VACUUM FULL is dropping the OS used space and then it fills up again
> as the database does those updates.
>
>> Based on your email it sounds like the vacuuming of those pg_catalog.*
>> tables is done regardless, as a normal part of doing vac full on my own
>> database.
>> Unfortunately I still don't see an ideal way to run vacuum full in parallel
>> via vacuumdb without running into the expected and documented deadlock. Only
>> method I'm aware of is to list each table individually with "-t table1 -t
>> table2..." to "vacuum db --jobs" which is not pleasant and not exceedingly
>> beautiful.
>> Thanks.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
Re: Connecting Powerbuilder / EAserver to PostgreSQL server 11 via ODBC or JDBC
System level call trace might help little bit using strace/truss utility. Regards, Virendra Kumar On Wednesday, July 1, 2020, 9:26:09 PM PDT, Rene de Gek wrote: Hi Adrian, Thank you for your reply. This was one of the things that Matthias has also suggested to us, referring to https://sybase.public.easerver.powerbuilder.narkive.com/MsoAXSQJ/problem-connecting-to-database-from-easerver-component. We do set the server type to ODBC. The error 999 shows up with several error messages. For example: When we connect to PostgreSQL using ODBC but we forget to fill in sqlca.DBMS, the application log has 999 without an errortext: fnv_connect: -1DBHandle is 0Setting up n_tr DBMS: DBParm: CacheName='pgsqlODBC12' Autocommit: false connect using i_tr... dberror event triggered on n_tr code: 999 sqlerrortext: sqlsyntax: SQLErrText: sqldbcode: 999 sqlcode: -1 When we try to connect to ODBC, but we set sqlca.DBMS incorrectly to JDBC, the code is 999, but there is a message "sqlerrortext: Java Exception : Fatal Error. Unable to initialize DatabaseMetaData class." fnv_connect: -1DBHandle is 0 Setting up n_tr DBMS: JDBCDBParm: CacheName='pgsqlODBC12' Autocommit: false connect using i_tr... dberror event triggered on n_tr code: 999 sqlerrortext: Java Exception : Fatal Error. Unable to initialize DatabaseMetaData class. sqlsyntax: SQLErrText: Java Exception : Fatal Error. Unable to initialize DatabaseMetaData class. sqldbcode: 999 sqlcode: -1 When connecting to ODBC using the correct sqlca.DBMS=ODBC, we also get the 999, but with the errortext about the invalid handle. fnv_connect: -1DBHandle is 0 Setting up n_tr DBMS: ODBC DBParm: CacheName='pgsqlODBC12' Autocommit: false connect using i_tr... dberror event triggered on n_tr code: 999 sqlerrortext: An invalid handle was passed in a call to the database driver. sqlsyntax: SQLErrText: An invalid handle was passed in a call to the database driver. sqldbcode: 999 sqlcode: -1 Unfortunately, it seems that the error 999 can have several causes. On the EAServer side we have tried all kind of things. If the connection log from the driver looks okay I am afraid we have ran out of ideas. Adrian Klaver wrote: A quick search found that this code be returned if the correct server type is not specified in EAServer.
xact_start time set to very old value
Hi Team, We have observed that xact_start time is null for some period and suddenly it populates to very old value. We have a query which runs every minute to report any transactions running for long time. That query reports nothing continuously for a minute before and suddenly it reports one transaction which is running for more than one day. Is there something wrong here in populating xact_start time in pg_stat_activity? Shall we go with any other approach to detect long running transaction in database? Can someone guide on this? I went through a thread - https://www.postgresql.org/message-id/20140424101827.2714.39486%40wrigleys.postgresql.org but not sure where it concluded. Appreciate your response. We are using AWS RDS 11.4. Regards,-Virendra
Copy Statistics Tables During Upgrade
Hello Team, I was doing an upgrade of one of our PG (9.6.11 to 11.9) database and came across the question that what is reason PostgreSQL is not doing copy of stats and we have to analyze right after upgrade. There are two situations where this will help when we use this DB as our enterprise database: 1. If DB is in magnitude of hundreds of TB and we have to do analyze it might take several hours before we handover this to application services. 2. We are loosing critical information like most common values and most common freqs because these values are populated over time when many-many sessions hit table and queries with different values of a column. Any input on this is higly appreciated. Regards, Virendra Kumar
Prepare Statement VS Literal Values
ehouse_owner_group_id , this_.warehouse_txn_type_id , this_.item_disposition_code , this_.last_updated , this_.last_updated_by , this_.last_updated_date , this_.on_hand_quantity , this_.original_quantity , this_.record_version_number , this_.warehouse_id from warehouse_costs this_ where this_.warehouse_id='IMXK' and this_.asin='B002LA1D9Y' and this_.warehouse_owner_group_id=1 and this_.item_disposition_code='SELLABLE' order by this_.cost_acquisition_date asc; QUERY PLAN -- Sort (cost=50630.82..50630.84 rows=7 width=160) (actual time=806.613..806.614 rows=5 loops=1) Sort Key: cost_acquisition_date Sort Method: quicksort Memory: 26kB Buffers: shared hit=13110 read=13163 -> Seq Scan on warehouse_costs this_ (cost=0.00..50630.73 rows=7 width=160) (actual time=341.937..806.582 rows=5 loops=1) Filter: ((warehouse_owner_group_id = '1'::numeric) AND ((item_disposition_code)::text = 'SELLABLE'::text) AND ((warehouse_id)::text = 'IMXK'::text) AND ((asin)::text = 'B002LA1D9Y'::text)) Rows Removed by Filter: 974304 Buffers: shared hit=13110 read=13163 Execution time: 806.652 ms (9 rows) Time: 807.352 ms testdb=# testdb=# explain analyze testdb-# select testdb-# this_.warehouse_cost_id , this_.asin , this_.base_currency_code , testdb-# this_.cost , this_.cost_acquisition_date , this_.cost_reference_id , testdb-# this_.cost_reference_id_source , this_.created_by , this_.creation_date , testdb-# this_.gl_product_group , this_.warehouse_fiscal_owner_id , testdb-# this_.warehouse_owner_group_id , this_.warehouse_txn_type_id , testdb-# this_.item_disposition_code , this_.last_updated , this_.last_updated_by , testdb-# this_.last_updated_date , this_.on_hand_quantity , this_.original_quantity , testdb-# this_.record_version_number , this_.warehouse_id from warehouse_costs this_ testdb-# where this_.warehouse_id='IMXK' and this_.asin='B002LA1D9Y' and this_.warehouse_owner_group_id=1 and this_.item_disposition_code='SELLABLE' testdb-# order by this_.cost_acquisition_date asc; QUERY PLAN -- Sort (cost=8.46..8.47 rows=1 width=160) (actual time=0.115..0.116 rows=5 loops=1) Sort Key: cost_acquisition_date Sort Method: quicksort Memory: 26kB -> Index Scan using i_ic_asin_whse_disp_iog_qty on warehouse_costs this_ (cost=0.42..8.45 rows=1 width=160) (actual time=0.070..0.083 rows=5 loops=1) Index Cond: ((asin = 'B002LA1D9Y'::bpchar) AND (warehouse_id = 'IMXK'::bpchar) AND ((item_disposition_code)::text = 'SELLABLE'::text) AND (warehouse_owner_group_id = '1'::numeric)) Planning time: 0.530 ms Execution time: 0.189 ms (7 rows) Time: 1.710 ms testdb=# -- As we can see the first plan is a prepared statement and is using seq scan of the table and took 800 ms while the second one is with literals and index scan and took 0.189 seconds. Is there a way I can force the prepared statement to go same as the values with literals. Thank you in advance! Regards, Virendra Kumar
Re: Prepare Statement VS Literal Values
Thank you for the pointer, here is plan now, it is much better:
testdb=# prepare fooplan(character varying,character varying ,bigint,character
varying) AS select
testdb-# this_.warehouse_cost_id , this_.asin , this_.base_currency_code,
testdb-# this_.cost , this_.cost_acquisition_date , this_.cost_reference_id ,
testdb-# this_.cost_reference_id_source , this_.created_by ,
this_.creation_date ,
testdb-# this_.gl_product_group , this_.warehouse_fiscal_owner_id ,
testdb-# this_.warehouse_owner_group_id , this_.warehouse_txn_type_id ,
testdb-# this_.item_disposition_code , this_.last_updated ,
this_.last_updated_by ,
testdb-# this_.last_updated_date , this_.on_hand_quantity ,
this_.original_quantity ,
testdb-# this_.record_version_number , this_.warehouse_id from warehouse_costs
this_
testdb-# where this_.warehouse_id=$1
testdb-# and this_.asin=$2
testdb-# and this_.warehouse_owner_group_id=$3
testdb-# and this_.item_disposition_code=$4
testdb-# order by this_.cost_acquisition_date asc;
PREPARE
testdb=# explain (analyze, buffers) execute
fooplan('IMXK','B002LA1D9Y','1','SELLABLE');
QUERY PLAN
--
Sort (cost=8.46..8.47 rows=1 width=160) (actual time=0.108..0.110 rows=5
loops=1)
Sort Key: cost_acquisition_date
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=8
-> Index Scan using i_ic_asin_whse_disp_iog_qty on warehouse_costs this_
(cost=0.42..8.45 rows=1 width=160) (actual time=0.067..0.080 rows=5 loops=1)
Index Cond: ((asin = 'B002LA1D9Y'::bpchar) AND (warehouse_id =
'IMXK'::bpchar) AND ((item_disposition_code)::text = 'SELLABLE'::text) AND
(warehouse_owner_group_id = '1'::numeric))
Buffers: shared hit=8
Execution time: 0.238 ms
(8 rows)
Regards,
Virendra Kumar
On Monday, April 12, 2021, 4:18:01 AM PDT, Vijaykumar Jain
wrote:
I am sorry if i have messed up the email original content.
feel free to ignore my diversion.
i forwarded this mail from some other account to work on the issue.
On Mon, Apr 12, 2021 at 4:45 PM Vijaykumar Jain
wrote:
>
> Hi,
>
> I am not sure I am 100% on this, because I have a small machine at home.
>
> but reading PostgreSQL: Documentation: 9.6: PREPARE and
> PostgreSQL - general - bpchar, text and indexes (postgresql-archive.org)
>
> PostgreSQL: Documentation: 9.6: Operators
>
> I think it might be something to do with the parameters types in the prepared
> statement and their casting.
>
> But , the below would be reproducible
>
> postgres=# create table t(t_id int4, sn_c char(20));
> CREATE TABLE
> postgres=# insert into t select id,
> chr((random()*26)::int4+65)||chr((random()*26)::int4+65)||((random()*9)::int4+1)
> from generate_series(1, 1) id;
> INSERT 0 1
> postgres=# create index i_t_sn_c on t(sn_c);
> CREATE INDEX
> postgres=# vacuum analyze t;
> VACUUM
> postgres=# EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c =
> 'AB1234';
> QUERY PLAN
> ---
> Index Only Scan using i_t_sn_c on t (actual time=0.048..0.048 rows=0 loops=1)
> Index Cond: (sn_c = 'AB1234'::bpchar)
> Heap Fetches: 0
> Planning Time: 0.119 ms
> Execution Time: 0.059 ms
> (5 rows)
>
> postgres=# EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c =
> 'AB1234'::text;
> QUERY PLAN
> -
> Seq Scan on t (actual time=3.037..3.037 rows=0 loops=1)
> Filter: ((sn_c)::text = 'AB1234'::text)
> Rows Removed by Filter: 1
> Planning Time: 0.057 ms
> Execution Time: 3.047 ms
> (5 rows)
>
> postgres=# prepare qq(text) as SELECT sn_c FROM t WHERE sn_c = $1; // verify
> from select * from pg_prepared_statements.
> PREPARE
> postgres=# explain analyze execute qq('AB1234');
> QUERY PLAN
> ---
> Seq Scan on t (cost=0.00..224.00 rows=50 width=21) (actual
> time=3.156..3.157 rows=0 loops=1)
> Filter: ((sn_c)::text = 'AB1234'::text)
> Rows Removed by Filter: 1
> Planning Time: 0.069 ms
> Execution Time: 3.168 ms
> (5 rows)
>
> postgres=# prepare qq(text) ^C S
