Re: Setting up streaming replication on large database (20+ TB) for the first time

I just did a backup and restore of a replica using pgbackrest. db size 28tb nvme/ssd storage 96 cpu, 380 gb mem zst compression, 24 workers (backup, 12 workers restore) 2.5 hours to backup 2 hours to restore. Wal replay is something I forgot to tune, but I could now use https://pgbackrest.org/co

Is it possible to keep indexes on different disk location?

Hi there, I have a PostgreSQL 11 server on OrangePi3 (ARM 64 bit, Armbian, PG from distro). Database (all cluster) is located on USB disk. This approach give me already 2 times loosing DB contents (it is a replica of DB on i7). But the whole thing (mainly indexes) is about 50G, and internal

Re: Unable to Create or Drop Index Concurrently

> On Aug 17, 2022, at 22:57, Abdul Qoyyuum wrote: > Question is, do we have to shutdown traffic and close all existing open > connections in order to drop and properly recreate the index? No, you don't. On the CREATE INDEX CONCURRENTLY command, what is likely going on is that when the conne

Unable to Create or Drop Index Concurrently

Hi list, We have a running Master-Slave High Availability set up. Naturally, we can't run any changes on read-only databases on slave, so we have to do it on the master node. When trying to run the following command: create index concurrently idx_cash_deposit_channel_id_batch_id on cash_deposit

Re: Setting up streaming replication on large database (20+ TB) for the first time

pg_backrest will certainly backup your data faster. It might be able to be used as a seed instead of pg_basebackup. On 8/17/22 15:06, Ivan N. Ivanov wrote: I have a large database (~25 TB) and I want to set up streaming replication for the first time. My problem is that after completion of th

Re: Different execution plan between PostgreSQL 8.2 and 12.5

On 8/17/22 20:01, gzh wrote: Hi, I have had a Perl Website working for 7 years and have had no problems until a few weeks ago I replaced my database server with a newer one. Did you run ANALYZE on the 12.5 server after restoring the data to it? gzh -- Adrian Klaver adrian.kla...@akl

Re: Different execution plan between PostgreSQL 8.2 and 12.5

On Thu, 18 Aug 2022 at 15:32, Tom Lane wrote: > The 12.5 plan looks like it thinks that the join condition is not > hashable --- and probably not mergeable as well, else it would have > done a mergejoin. This is odd if we assume that the lower() > outputs are just text. But you haven't said anyt

Re: Different execution plan between PostgreSQL 8.2 and 12.5

gzh writes: > I run following sql in PostgreSQL 8.2 and PostgreSQL 12.5, it returns > different execution plan. 8.2 is ... well, not stone age maybe, but pretty durn ancient. You really ought to update a bit more often than that. (And maybe pay more attention to staying up to date with minor re

Different execution plan between PostgreSQL 8.2 and 12.5

Hi, I have had a Perl Website working for 7 years and have had no problems until a few weeks ago I replaced my database server with a newer one. Database server (old): PostgreSQL 8.2 32bit Database server (new): PostgreSQL 12.5 64bit I run following sql in PostgreSQL 8.2 and PostgreS

Re: [(catch-ext)] Re: Setting up streaming replication on large database (20+ TB) for the first time

Thank you for your answer! I have found this tool and I will try it tomorrow to see if this "read-ahead" feature will speed up the process. On Wed, Aug 17, 2022 at 11:09 PM Christophe Pettus wrote: > > > > On Aug 17, 2022, at 13:06, Ivan N. Ivanov > wrote: > > > > How to speed up recovering of

Re: Setting up streaming replication on large database (20+ TB) for the first time

> On Aug 17, 2022, at 13:06, Ivan N. Ivanov wrote: > > How to speed up recovering of WAL files? Since you are running on your own hardware, you might take a look at: https://github.com/TritonDataCenter/pg_prefaulter

Setting up streaming replication on large database (20+ TB) for the first time

I have a large database (~25 TB) and I want to set up streaming replication for the first time. My problem is that after completion of the pg_basebackup (which completed for 2 days with --wal-method=none) now PG is replaying the WAL files from the WAL archive directory but it can not keep up. The

Re: i added Arabic Dictionary but how I know i'm using it

On Wed, 2022-08-17 at 14:33 +0300, Mohammed falih wrote: > I created the dictionary by this query  > CREATE TEXT SEARCH DICTIONARY arabic_dict ( >     TEMPLATE = ispell, >    DictFile = th_ar, >     AffFile = th_ar, >     Stopwords = arabic); > and as you know the purpose in dictionary is like when

Re: Sub:column "" is of type bigint but expression is of type character varying

On Wed, Aug 17, 2022 at 5:13 AM Rama Krishnan wrote: > Hi All, > > I am having table name called tickets > > > \d tickets > > Column|Type | Collation | > Nullable | Default > > --+-+---

Re: Sub:column "" is of type bigint but expression is of type character varying

On 8/17/22 01:53, Rama Krishnan wrote: Hi All, i have purged the old data from orginal table when i am restoring the data from archive table into orignal table i am getting the error *insert into tickets select * from tickets_archive;* The above depends on: https://www.postgresql.org/

Re: Postgres question

ajay venki schrieb am 17.08.2022 um 14:39: > Thanks. I am looking forward to install pgloader tool to migrate my MS SQL > data to postgres.  > I tried searching it online and the instructions were not clear to me. Is > there any article or video which talks about pgloader installation on windows

Re: Postgres question

Thanks. I am looking forward to install pgloader tool to migrate my MS SQL data to postgres. I tried searching it online and the instructions were not clear to me. Is there any article or video which talks about pgloader installation on windows 10 ? Thanks Ajay On Wed, Aug 17, 2022 at 1:24 PM hub

Re: Sub:column "" is of type bigint but expression is of type character varying

1. Please check you tickets_archive.ticket_purchase_no data type. 2. check if you are using correct schema. On 8/17/22 14:23, Rama Krishnan wrote: Hi All, I am having table name called tickets \d tickets             Column            |     Type             | Collation | Nullable | Default

Re: Postgres question

On Wed, Aug 17, 2022 at 10:32:26AM +0100, ajay venki wrote: > I am new to PostgreSQL and i have a general question to clarify. is this > the right forum or the mail address to post my questions? Yes, this is the right place. Best regards, depesz

Postgres question

Hi , I am new to PostgreSQL and i have a general question to clarify. is this the right forum or the mail address to post my questions? Thanks

Sub:column "" is of type bigint but expression is of type character varying

Hi All, I am having table name called tickets \d tickets Column|Type | Collation | Nullable | Default --+-+---+--+- id | bigint

Re: Regarding availability of 32bit client drivers for postgresql 13/14

WHY 32-bit in 2022 under RHEL 8.5? On 8/16/22 23:53, Aravind Phaneendra wrote: Thank you Adrian for the response, We are looking for 32bit client drivers for RHEL 8.5. An another question .. How does the enterprise customers using PostgreSQL can subscribe to official support ? Thanks & Reg

i added Arabic Dictionary but how I know i'm using it

Greetings Dear I hope you are fine and doing well. I have a created a dictionary in postgres successfully but how may I know that I'm using it? because I'm doing now queries and the results are the same before I created the dictionary I downloaded an rar folder that I supposed its a dictionar

Re: Can I get the number of results plus the results with a single query?

On 2022-08-16 14:42:48 -0700, Bryn Llewellyn wrote: > hjp-pg...@hjp.at wrote: > The OP wants some kind of progress indicator. To be useful, such > an indicator should be approximately linear in time. I.e. if your [...] > > > I see, Peter. You’d read the OP’s mind. Not much mind-readi