Re: Seeded Replication

Thank you Adrian. copy_data = false did the trick! Lou Tseng lts...@advancedpricing.com [http://advancedpricing.com/wp-content/uploads/logo_AMPS_email.png] Advanced Medical Pricing Solutions 35

Re: PostgreSQL Windows 2019 support ?

On Wed, Apr 03, 2019 at 01:12:56PM -0700, Andres Freund wrote: > I don't think VS2019 and Windows 2019 are the same thing... And the > latter has been out for longer than yesterday... I don't know if > anybody has done rigorous testing on it however. Yes, it seems to me that we talk here about

Re: Reg: Pg_Ctl command help

When you open a command shell to execute pg_ctl, all commands, including Postgresql will be terminated once you close the command shell. As Rene stated, the proper way to start and stop PostgreSQL is to use the Services from the Task Manager. On Wed, Apr 3, 2019 at 8:30 PM Rene Romero Benavides <

Re: Reg: Pg_Ctl command help

How did you install it? It should be installed as a service. On Wed, Apr 3, 2019 at 4:24 AM Nadeem Akbar basha wrote: > Hello, > > > > I have a query regarding starting the Postgres server using the ‘pg_ctl’ > command in the command prompt (Windows OS). > > > > I try to start the postgres

Re: Seeded Replication

On 4/3/19 4:44 AM, Lou Tseng wrote: Hi, We are working on a project to set up replication from on premises Postgresql 10.4 to Azure Postgresql services. However, our database is about 200G and it will take a long time to complete the initial data copy.  We would like to manually seed the

Re: stale WAL files?

On Wed, Apr 3, 2019 at 1:05 PM Rob Sargent wrote: > > > On Apr 1, 2019, at 9:20 PM, Rene Romero Benavides > wrote: > > > On Mon, Apr 1, 2019 at 6:30 PM Rene Romero Benavides < > rene.romer...@gmail.com> wrote: > >> >> On Sat, Mar 30, 2019 at 5:03 PM Gmail wrote: >> >>> >>> >>> > On Mar 30,

Re: Recommendation to run vacuum FULL in parallel

On 4/3/19 3:45 PM, Perumal Raj wrote: Hi Stephen Thanks for the response , Version : 9.2 We never ran VACUUM FULL in the past, All we are doing just manual vacuum ( Weekly ) . Based on the Observation ( test run ) , we were able to reclaim 150 GB out of 500 GB . We are heading to a planned

Re: Recommendation to run vacuum FULL in parallel

Hi Stephen Thanks for the response , Version : 9.2 We never ran VACUUM FULL in the past, All we are doing just manual vacuum ( Weekly ) . Based on the Observation ( test run ) , we were able to reclaim 150 GB out of 500 GB . We are heading to a planned down time soon , So thinking to run FULL

Re: Recommendation to run vacuum FULL in parallel

> Ideally VACUUM FULL should not require a giant lock on the table. It is a massively expensive operation, regardless. Not sure if it is something you want to run in production outside a maintenance window. I would argue that frequent vacuum full is an antipattern. This will become a matter of

Re: PostgreSQL Windows 2019 support ?

Hi, On 2019-04-03 12:43:25 -0400, Tom Lane wrote: > david moloney writes: > > Is PostgreSQL 10+ supported on windows 2019 ? > > It is not. Considering that VS2019 was released yesterday, > you should have been surprised to get any other answer. I don't think VS2019 and Windows 2019 are the

Re: Forcing index usage

Greetings, * Michael Lewis (mle...@entrata.com) wrote: > Thanks for that advance warning since it is a handy option to force the > planning barrier in my experience. What's a resource to see other coming > changes in v12 especially changes to default behavior like this? Will there > be a new

Re: Forcing index usage

Thanks for that advance warning since it is a handy option to force the planning barrier in my experience. What's a resource to see other coming changes in v12 especially changes to default behavior like this? Will there be a new cte_collapse_limit setting or similar?

Re: stale WAL files?

> On Apr 1, 2019, at 9:20 PM, Rene Romero Benavides > wrote: > > > On Mon, Apr 1, 2019 at 6:30 PM Rene Romero Benavides > wrote: > > On Sat, Mar 30, 2019 at 5:03 PM Gmail > wrote: > > > > On Mar 30, 2019, at 10:54 AM, Gmail

Re: Forcing index usage

Greetings, * Michael Lewis (mle...@entrata.com) wrote: > > Is there a way to tell Postgres “please don’t use index X when queries > > that could use index Y instead occur?” > > No. But you could re-write the query to make the date index useless. The > simplest way that comes to mind is putting

Re: Recommendation to run vacuum FULL in parallel

Hi All Thanks for all your valuable inputs, Here is some more data, Though we have 150 GB free space spread across 500 Tables , Every alternative day DB is growing with 1 GB rate. Also,We have manual vacuum job scheduled to run weekly basis, So seems to be space is not reusing all the time ?

Re: Forcing index usage

> > Is there a way to tell Postgres “please don’t use index X when queries > that could use index Y instead occur?” > No. But you could re-write the query to make the date index useless. The simplest way that comes to mind is putting the query that does your full-text search in a CTE (WITH

Re: PostgreSQL Windows 2019 support ?

david moloney writes: > Is PostgreSQL 10+ supported on windows 2019 ? It is not. Considering that VS2019 was released yesterday, you should have been surprised to get any other answer. > If not is there a rough time frame for when it will be supported on this OS ? Whenever this patch gets

Re: Lingering replication slots

Thank you. Wasn't able to drop replication slot because it's still active. Arjun suggested pg_terminate_backend() and it did the trick. Thanks a lot! Lou Tseng lts...@advancedpricing.com

Re: stale WAL files?

Sorry folks, I’m still home nursing a nasty chest cold and my only tool today is an iPad. I have failed to get the postgresql.conf into the copy buffer so that, along with the results of pg_settings, will have to wait for another day. Today there are “only” 135 Mar 16 WAL files. I haven’t

Re: Lingering replication slots

On 4/3/19 9:01 AM, Lou Tseng wrote: Hi, A newbie question: how do I delete lingering replication slots?  When creating Subscription for logical replication, the CREATE SUBSCRIPTION stuck and didn't return. After ctrl-c and aborted the command, the master database have lingering replication

PostgreSQL Windows 2019 support ?

Hi, Is PostgreSQL 10+ supported on windows 2019 ? If not is there a rough time frame for when it will be supported on this OS ? Thanks, David

Forcing index usage

Hi folks — We’ve got several tables that are relatively large (~250-500GB in total size) that use very large (tens to hundreds of GB) GIN indexes for full-text search. We’ve set the column statistics for our tsvector columns as high as they go (10,000). We almost always order our search

Lingering replication slots

Hi, A newbie question: how do I delete lingering replication slots? When creating Subscription for logical replication, the CREATE SUBSCRIPTION stuck and didn't return. After ctrl-c and aborted the command, the master database have lingering replication slots that I can't delete because the

Re: Recommendation to run vacuum FULL in parallel

And future updates can reuse it, too (an update is very similar to an insert+delete). Hm, then it's strange our DB takes 6 times as much space compared to freshly restored one (only public schema is considered). Not if autovacuum has a chance to run between updates. Ours is run

Re: Move vs. copy table between databases that share a tablespace?

Alvaro Herrera writes: > On 2019-Apr-03, Tom Lane wrote: >> Actually, thinking about that a bit harder: there's one aspect of >> what pg_upgrade does that's really hard to control from userspace, >> and that's forcing tables to have the same OIDs as before. In this >> context, that means you're

Re: Move vs. copy table between databases that share a tablespace?

On 2019-Apr-03, Tom Lane wrote: > I wrote: > > Steven Lembark writes: > >> Given that the two databases live in the same cluster and have > >> the owner & the tablespace in common, is there any way to move > >> the contents without a dump & reload? > > > In principle you could do that; it's

Re: Recommendation to run vacuum FULL in parallel

On 2019-04-03 18:49:02 +0400, rihad wrote: > On 04/03/2019 06:40 PM, Michael Lewis wrote: > > "Sometimes a table's usage pattern involves much more updates than > > inserts, which gradually uses more and more unused space that is never > > used again by postgres, and plain autovacuuming doesn't

Re: Move vs. copy table between databases that share a tablespace?

I wrote: > Steven Lembark writes: >> Given that the two databases live in the same cluster and have >> the owner & the tablespace in common, is there any way to move >> the contents without a dump & reload? > In principle you could do that; it's more or less the same thing that > pg_upgrade

Re: Recommendation to run vacuum FULL in parallel

On 04/03/2019 06:40 PM, Michael Lewis wrote: "Sometimes a table's usage pattern involves much more updates than inserts, which gradually uses more and more unused space that is never used again by postgres, and plain autovacuuming doesn't return it to the OS." Can you expound on that? I thought

Re: Seeded Replication

Pavan, This is the steps I took. I think I did it right: [Master] pg_dump -s -f schema.sql pg_dump -a -f data.sql CREATE PUBLICATION [Client/Slave] psql \i schema.sql CREATE SUBSCRIPTION ... WITH (enabled = false) SET session_replication_role = replica; -- turn off trigger psql \i data.sql

Re: Recommendation to run vacuum FULL in parallel

"Sometimes a table's usage pattern involves much more updates than inserts, which gradually uses more and more unused space that is never used again by postgres, and plain autovacuuming doesn't return it to the OS." Can you expound on that? I thought that was exactly what autovacuum did for old

Re: Move vs. copy table between databases that share a tablespace?

On 4/3/19 7:28 AM, Steven Lembark wrote: Through a pipe to a different server. No access to a server w/ sufficient space. Am I correct in assuming that the long term goal is to create more space? Otherwise I am not sure what the below is going to get you? Looks like a compressed dump +

Re: Move vs. copy table between databases that share a tablespace?

> Through a pipe to a different server. No access to a server w/ sufficient space. Looks like a compressed dump + restore... Thanks -- Steven Lembark 3920 10th Ave South Workhorse Computing Birmingham, AL 35222

Re: Move vs. copy table between databases that share a tablespace?

On 4/3/19 8:39 AM, Steven Lembark wrote: On Wed, 3 Apr 2019 08:33:54 -0500 Ron wrote: On 4/3/19 8:18 AM, Steven Lembark wrote: Trying to find a way of moving a large table between databases in the same cluster. There is not sufficient space to copy the contents -- the dedicated tablespace

Re: Move vs. copy table between databases that share a tablespace?

Steven Lembark writes: > Trying to find a way of moving a large table between databases > in the same cluster. There is not sufficient space to copy the > contents -- the dedicated tablespace that fits the beastie is > on an 80% full disk. > Given that the two databases live in the same cluster

Re: Move vs. copy table between databases that share a tablespace?

On Wed, 3 Apr 2019 08:33:54 -0500 Ron wrote: > On 4/3/19 8:18 AM, Steven Lembark wrote: > > Trying to find a way of moving a large table between databases > > in the same cluster. There is not sufficient space to copy the > > contents -- the dedicated tablespace that fits the beastie is > > on

Sv: SQL queries not matching on certain fields

På onsdag 03. april 2019 kl. 15:06:03, skrev Felix Ableitner mailto:m...@nutomic.com>>: Hello, I'm having a very strange problem with the Postgres database for my website. Some SQL queries are not matching on certain fields. I am running these commands via the psql command. Here is a query

Re: Move vs. copy table between databases that share a tablespace?

On 4/3/19 8:18 AM, Steven Lembark wrote: Trying to find a way of moving a large table between databases in the same cluster. There is not sufficient space to copy the contents -- the dedicated tablespace that fits the beastie is on an 80% full disk. Given that the two databases live in the same

Re: SQL queries not matching on certain fields

Broken index? I had a similar problem a while ago. We were streaming a DB from a Debian box to an alpine docker image. The underlying system libraries were a little different and that resulted in broken index behavior. On Wed, Apr 3, 2019 at 3:06 PM Felix Ableitner wrote: > Hello, > > I'm

Re: SQL queries not matching on certain fields

> On Apr 3, 2019, at 2:06 PM, Felix Ableitner wrote: > > Hello, > > I'm having a very strange problem with the Postgres database for my website. > Some SQL queries are not matching on certain fields. I am running these > commands via the psql command. > > Here is a query that works as

Move vs. copy table between databases that share a tablespace?

Trying to find a way of moving a large table between databases in the same cluster. There is not sufficient space to copy the contents -- the dedicated tablespace that fits the beastie is on an 80% full disk. Given that the two databases live in the same cluster and have the owner & the

SQL queries not matching on certain fields

Hello, I'm having a very strange problem with the Postgres database for my website. Some SQL queries are not matching on certain fields. I am running these commands via the psql command. Here is a query that works as expected: # SELECT id, "preferredUsername" FROM actor WHERE

Re: Seeded Replication

Hi Lou, Did you restore data on the subscriber during the first time. If yes this error is likely to occur. As a remedy, you need to restore only structural dump during initial building of subscription. Earlier the same issue was faced by me later it got resolved following the above said

Seeded Replication

Hi, We are working on a project to set up replication from on premises Postgresql 10.4 to Azure Postgresql services. However, our database is about 200G and it will take a long time to complete the initial data copy. We would like to manually seed the subscriber database with data dump and

Re: Recommendation to run vacuum FULL in parallel

On 2019-04-03 13:12:56 +0400, rihad wrote: > Ideally VACUUM FULL should not require a giant lock on the table. [...] > Since rewriting a table is a completely internal operation from > clients' POV, hopefully one day we will see a concurrent version of > vacuum full. There are (at least)

Reg: Pg_Ctl command help

Hello, I have a query regarding starting the Postgres server using the 'pg_ctl' command in the command prompt (Windows OS). I try to start the postgres server using the following command, pg_ctl -D "C:\Program Files\PostgreSQL\9.6\data" start -w After the server gets started, I'm closing the

Re: Recommendation to run vacuum FULL in parallel

Does *every* table have *so much* free space that it's impractical to just let the files just get refilled by normal usage? Ideally VACUUM FULL should not require a giant lock on the table. Sometimes a table's usage pattern involves much more updates than inserts, which gradually uses more

Re: New LLVM JIT Features

Hi Andres, Thanks for your thoughts , please see my comments inline. On 03/04/19 10:53 AM, Andres Freund wrote: On 2019-04-03 10:44:06 +0530, preejackie wrote: Hi Andres, Thanks for the reply! Please see my comments inline. On 03/04/19 3:20 AM, Andres Freund wrote: Hi, On 2019-04-02

Re: Recommendation to run vacuum FULL in parallel

Perumal Raj wrote: > We are planning to reclaim unused space from 9.2 Version postgres Cluster, > > Method : VACUUM FULL > DB Size : 500 GB > Expected space to reclaim 150 GB > work_mem : 250 MB > maintenance_work_mem : 20 GB > > Question : > > 1. vacuumdb --j option (Parallel) not available

Re: Recommendation to run vacuum FULL in parallel

On 4/3/19 12:50 AM, Perumal Raj wrote: Hi ALL We are  planning to reclaim unused space from 9.2 Version postgres Cluster, Method : VACUUM FULL Does *every* table have *so much* free space that it's impractical to just let the files just get refilled by normal usage? DB Size : 500 GB