Re: Seeded Replication

[Lou Tseng]

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 Technology Parkway South, Suite. 100
Peachtree Corners, GA 30092



From: Adrian Klaver 
Sent: Wednesday, April 3, 2019 7:10 PM
To: Lou Tseng; pgsql-gene...@postgresql.org
Subject: 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 subscriber database with data
> dump and then turn on the subscription like depesz showed in this post
> https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-logical-replication/
>  .
> It works for the small testing database but when I applied same steps
> with entire database, I am getting "ERROR:  duplicate key value violates
> unique constraint" errors. Basically postgresql does not recognize the
> primary key.

Actually it does which why you get the error, which by the way is the
same error depesz got in his demo:)

 From your subsequent post:

CREATE SUBSCRIPTION ... WITH (enabled = false)

Did the above also have?:

https://www.postgresql.org/docs/11/sql-createsubscription.html

copy_data = false

If not then you would copying over the data again and that would trigger
the duplicate key error.


>
> Any thought / suggestion is helpful!
>
> Lou Tseng
>
> lts...@advancedpricing.com 
> 
> Advanced Medical Pricing Solutions 
> 35 Technology Parkway South, Suite. 100
> Peachtree Corners, GA 30092
>
>


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: PostgreSQL Windows 2019 support ?

[Michael Paquier]

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 Windows SERVER 2019, which
has been released by Microsoft recently.  VS2019 is a different
thing.

> I'd be somewhat surprised if it didn't just work however.

Agreed.
--
Michael


signature.asc
Description: PGP signature

Re: Reg: Pg_Ctl command help

[Melvin Davidson]

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 <
rene.romer...@gmail.com> wrote:

> 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 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 command prompt. But as
>> soon as I close the console, the server gets forcefully shutdown. Again I
>> have to restart the server  through command prompt.
>>
>> Is there any way, where after starting the server, I have to exit the
>> command prompt, still running the server at the background.
>>
>>
>>
>> Please help me to resolve this issue.
>>
>>
>>
>> *Thanks & Regards,*
>>
>> *A. Nadeem Ahmed*
>>
>>
>> ::DISCLAIMER::
>>
>> --
>> The contents of this e-mail and any attachment(s) are confidential and
>> intended for the named recipient(s) only. E-mail transmission is not
>> guaranteed to be secure or error-free as information could be intercepted,
>> corrupted, lost, destroyed, arrive late or incomplete, or may contain
>> viruses in transmission. The e mail and its contents (with or without
>> referred errors) shall therefore not attach any liability on the originator
>> or HCL or its affiliates. Views or opinions, if any, presented in this
>> email are solely those of the author and may not necessarily reflect the
>> views or opinions of HCL or its affiliates. Any form of reproduction,
>> dissemination, copying, disclosure, modification, distribution and / or
>> publication of this message without the prior written consent of authorized
>> representative of HCL is strictly prohibited. If you have received this
>> email in error please delete it and notify the sender immediately. Before
>> opening any email and/or attachments, please check them for viruses and
>> other defects.
>> --
>>
>>
>
>
> --
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/
>
>

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: Reg: Pg_Ctl command help

[Rene Romero Benavides]

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 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 command prompt. But as soon
> as I close the console, the server gets forcefully shutdown. Again I have
> to restart the server  through command prompt.
>
> Is there any way, where after starting the server, I have to exit the
> command prompt, still running the server at the background.
>
>
>
> Please help me to resolve this issue.
>
>
>
> *Thanks & Regards,*
>
> *A. Nadeem Ahmed*
>
>
> ::DISCLAIMER::
>
> --
> The contents of this e-mail and any attachment(s) are confidential and
> intended for the named recipient(s) only. E-mail transmission is not
> guaranteed to be secure or error-free as information could be intercepted,
> corrupted, lost, destroyed, arrive late or incomplete, or may contain
> viruses in transmission. The e mail and its contents (with or without
> referred errors) shall therefore not attach any liability on the originator
> or HCL or its affiliates. Views or opinions, if any, presented in this
> email are solely those of the author and may not necessarily reflect the
> views or opinions of HCL or its affiliates. Any form of reproduction,
> dissemination, copying, disclosure, modification, distribution and / or
> publication of this message without the prior written consent of authorized
> representative of HCL is strictly prohibited. If you have received this
> email in error please delete it and notify the sender immediately. Before
> opening any email and/or attachments, please check them for viruses and
> other defects.
> --
>
>


-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Re: Seeded Replication

[Adrian Klaver]

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 subscriber database with data 
dump and then turn on the subscription like depesz showed in this post 
https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-logical-replication/ . 
It works for the small testing database but when I applied same steps 
with entire database, I am getting "ERROR:  duplicate key value violates 
unique constraint" errors. Basically postgresql does not recognize the 
primary key.


Actually it does which why you get the error, which by the way is the 
same error depesz got in his demo:)


From your subsequent post:

CREATE SUBSCRIPTION ... WITH (enabled = false)

Did the above also have?:

https://www.postgresql.org/docs/11/sql-createsubscription.html

copy_data = false

If not then you would copying over the data again and that would trigger 
the duplicate key error.





Any thought / suggestion is helpful!

Lou Tseng

lts...@advancedpricing.com 

Advanced Medical Pricing Solutions 
35 Technology Parkway South, Suite. 100
Peachtree Corners, GA 30092





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: stale WAL files?

[Rene Romero Benavides]

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, 2019, at 10:54 AM, Gmail  wrote:
>>> >
>>> >
>>>  On Mar 29, 2019, at 6:58 AM, Michael Paquier 
>>> wrote:
>>> >>>
>>> >>> On Thu, Mar 28, 2019 at 09:53:16AM -0600, Rob Sargent wrote:
>>> >>> This is pg10 so it's pg_wal.  ls -ltr
>>> >>>
>>> >>>
>>> >>> -rw---. 1 postgres postgres 16777216 Mar 16 16:33
>>> >>> 00010CEA00B1
>>> >>> -rw---. 1 postgres postgres 16777216 Mar 16 16:33
>>> >>> 00010CEA00B2
>>> >>>
>>> >>> ... 217 more on through to ...
>>> >>>
>>> >>> -rw---. 1 postgres postgres 16777216 Mar 16 17:01
>>> >>> 00010CEA00E8
>>> >>> -rw---. 1 postgres postgres 16777216 Mar 16 17:01
>>> >>> 00010CEA00E9
>>> >>> -rw---. 1 postgres postgres 16777216 Mar 28 09:46
>>> >>> 00010CEA000E
>>> > I’m now down to 208 Mar 16 WAL files so they are being processed (at
>>> least deleted).  I’ve taken a snapshot of the pg_wal dir such that I can
>>> see which files get processed. It’s none of the files I’ve listed previously
>>>
>>> Two more have been cleaned up.  001C and 001D generated at 16:38 Mar 16
>>>
>>>
>>>
>>> Please share your complete postgresql.conf file and the results from
>> this query:
>> SELECT * FROM pg_settings;
>> has someone in the past configured wal archiving?
>> You've ran out of disk space as this log message you shared states:
>> No space left on device
>> what's the output of df -h
>>
>> --
>> El genio es 1% inspiración y 99% transpiración.
>> Thomas Alva Edison
>> http://pglearn.blogspot.mx/
>>
>>
> BTW , how spread apart are checkpoints happening? do you have stats on
> that? maybe they're too spread apart and that's why WAL files cannot be
> recycled rapidly enough?
> --
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/
>
> two attempts (one in-line, one with attachement) at sending
> postgresql.conf and pg_settings report have been sent to a moderator.
>
>
>
As per your configuration :
max_wal_size = 50GB
this seems to be the cause for the WAL files piling up.

this has been declared twice, the last one is taking effect.
-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Re: Recommendation to run vacuum FULL in parallel

[Ron]

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 down time soon , So thinking to run FULL 
during that time .


Reason behind to run FULL : 1. Reclaim unused space which postgres never 
using it.


Did you purge a *lot* of records?

                                                2. Considering  FULL may 
increase the performance.


Maybe. But choose your tables wisely.

                                                3. Daily backup size and 
time  will be reduced after reclaiming 150GB.


How are you currently performing backups?  (The size won't change if you're 
using pg_dump, and it won't change much if you're using pgbackrest with the 
compression option -- thought it will probably run faster.)


Bottom line:

1. choose your tables wisely.
2. make sure you have enough disk space.
3. Either autovacuum more aggressively or explicitly vacuum certain tables 
from a cron job.




Thanks,
Raj


On Wed, Apr 3, 2019 at 1:16 PM Stephen Eilert > wrote:


> 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 superstition in your company.

If db size growth is a problem, make autovacuum more agressive. Or run
your manual vacuum job (not full) more often than a week. Daily, if
you have to. This will not reclaim disk space as reported by the OS,
but it should make the space available for new row versions, so db
should mostly stop growing from the OS point of view(mostly, because
you may be adding new data, right?). If it is still a problem, then
there may be something else going on.

Which PG version is that?


— Stephen
On Apr 3, 2019, 10:02 AM -0700, Perumal Raj mailto:peruci...@gmail.com>>, wrote:

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 ?

So conclude the requirement here , The only way to parallelism is
multiple script. And no need to do REINDEX exclusively.
Question : Do we need to consider  Table dependencies while preparing
script in order to avoid table locks during vacuum full ?

At present Maintenance work memory set to 20 GB.
Question : Do we need to tweak any other parameters ?

Note:
We are planning this activity with Application Downtime only.

Let me know if i missed anything.

Regards,
Raj





On Wed, Apr 3, 2019 at 8:42 AM rihad mailto:ri...@mail.ru>> wrote:

> 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 regularly, although we had to tweak it down not to
interfere
with normal database activity, so it takes several hours each run
on the
table. We did that by setting autovacuum_vacuum_scale_factor =
0.05 from
default 0.2.




--
Angular momentum makes the world go 'round.

Re: Recommendation to run vacuum FULL in parallel

[Perumal Raj]

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
during that time .

Reason behind to run FULL : 1. Reclaim unused space which postgres never
using it.
2. Considering  FULL may
increase the performance.
3. Daily backup size and
time  will be reduced after reclaiming 150GB.

Thanks,
Raj


On Wed, Apr 3, 2019 at 1:16 PM Stephen Eilert 
wrote:

> > 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 superstition in your company.
>
> If db size growth is a problem, make autovacuum more agressive. Or run
> your manual vacuum job (not full) more often than a week. Daily, if you
> have to. This will not reclaim disk space as reported by the OS, but it
> should make the space available for new row versions, so db should mostly
> stop growing from the OS point of view(mostly, because you may be adding
> new data, right?). If it is still a problem, then there may be something
> else going on.
>
> Which PG version is that?
>
>
> — Stephen
> On Apr 3, 2019, 10:02 AM -0700, Perumal Raj , wrote:
>
> 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 ?
>
> So conclude the requirement here , The only way to parallelism is multiple
> script. And no need to do REINDEX exclusively.
> Question : Do we need to consider  Table dependencies while preparing
> script in order to avoid table locks during vacuum full ?
>
> At present Maintenance work memory set to 20 GB.
> Question : Do we need to tweak any other parameters ?
>
> Note:
> We are planning this activity with Application Downtime only.
>
> Let me know if i missed anything.
>
> Regards,
> Raj
>
>
>
>
>
> On Wed, Apr 3, 2019 at 8:42 AM rihad  wrote:
>
>> > 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 regularly, although we had to tweak it down not to interfere
>> with normal database activity, so it takes several hours each run on the
>> table. We did that by setting autovacuum_vacuum_scale_factor = 0.05 from
>> default 0.2.
>>
>>
>>

Re: Recommendation to run vacuum FULL in parallel

[Stephen Eilert]

> 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 superstition in your company.

If db size growth is a problem, make autovacuum more agressive. Or run your 
manual vacuum job (not full) more often than a week. Daily, if you have to. 
This will not reclaim disk space as reported by the OS, but it should make the 
space available for new row versions, so db should mostly stop growing from the 
OS point of view(mostly, because you may be adding new data, right?). If it is 
still a problem, then there may be something else going on.

Which PG version is that?


— Stephen
On Apr 3, 2019, 10:02 AM -0700, Perumal Raj , wrote:
> 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 ?
>
> So conclude the requirement here , The only way to parallelism is multiple 
> script. And no need to do REINDEX exclusively.
> Question : Do we need to consider  Table dependencies while preparing script 
> in order to avoid table locks during vacuum full ?
>
> At present Maintenance work memory set to 20 GB.
> Question : Do we need to tweak any other parameters ?
>
> Note:
> We are planning this activity with Application Downtime only.
>
> Let me know if i missed anything.
>
> Regards,
> Raj
>
>
>
>
>
> > On Wed, Apr 3, 2019 at 8:42 AM rihad  wrote:
> > > > 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 regularly, although we had to tweak it down not to interfere
> > > with normal database activity, so it takes several hours each run on the
> > > table. We did that by setting autovacuum_vacuum_scale_factor = 0.05 from
> > > default 0.2.
> > >
> > >

Re: PostgreSQL Windows 2019 support ?

[Andres Freund]

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 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.

I'd be somewhat surprised if it didn't just work however.

Greetings,

Andres Freund

Re: Forcing index usage

[Stephen Frost]

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 cte_collapse_limit setting or similar?

Check the release notes.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Forcing index usage

[Michael Lewis]

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?

[Rob Sargent]

> 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  > > wrote:
> > 
> > 
>  On Mar 29, 2019, at 6:58 AM, Michael Paquier   > wrote:
> >>> 
> >>> On Thu, Mar 28, 2019 at 09:53:16AM -0600, Rob Sargent wrote:
> >>> This is pg10 so it's pg_wal.  ls -ltr
> >>> 
> >>> 
> >>> -rw---. 1 postgres postgres 16777216 Mar 16 16:33
> >>> 00010CEA00B1
> >>> -rw---. 1 postgres postgres 16777216 Mar 16 16:33
> >>> 00010CEA00B2
> >>> 
> >>> ... 217 more on through to ...
> >>> 
> >>> -rw---. 1 postgres postgres 16777216 Mar 16 17:01
> >>> 00010CEA00E8
> >>> -rw---. 1 postgres postgres 16777216 Mar 16 17:01
> >>> 00010CEA00E9
> >>> -rw---. 1 postgres postgres 16777216 Mar 28 09:46
> >>> 00010CEA000E
> > I’m now down to 208 Mar 16 WAL files so they are being processed (at least 
> > deleted).  I’ve taken a snapshot of the pg_wal dir such that I can see 
> > which files get processed. It’s none of the files I’ve listed previously
> 
> Two more have been cleaned up.  001C and 001D generated at 16:38 Mar 16
> 
> 
> 
> Please share your complete postgresql.conf file and the results from this 
> query: 
> SELECT * FROM pg_settings;
> has someone in the past configured wal archiving?
> You've ran out of disk space as this log message you shared states:
> No space left on device
> what's the output of df -h
> 
> -- 
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/ 
> 
> 
> BTW , how spread apart are checkpoints happening? do you have stats on that? 
> maybe they're too spread apart and that's why WAL files cannot be recycled 
> rapidly enough?  
> -- 
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/ 
> 
two attempts (one in-line, one with attachement) at sending postgresql.conf and 
pg_settings report have been sent to a moderator.

Re: Forcing index usage

[Stephen Frost]

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 the query that does your
> full-text search in a CTE (WITH keyword, it is an optimization boundary)
> and then ordering and applying your limit to the materialized set that
> comes out of that. eg.
> 
> WITH cte_full_text_results AS(
> SELECT date, result FROM big_a_table WHERE text = 'whatever'
> )
> SELECT * FROM cte_full_text_results ORDER BY date DESC limit 10;

Note that in v12, you'll need to include the MATERIALIZE keyword,
otherwise we'll in-line the CTE and you might get the plan you don't
like.  That said, it seems a bit unfortunate that there's no clear way
to create an index which specifically answers this query; figuring out a
way to do that could be very beneficial in a number of areas.  The RUM
index type attempts to improve things here, as I understand it.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Recommendation to run vacuum FULL in parallel

[Perumal Raj]

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 ?

So conclude the requirement here , The only way to parallelism is multiple
script. And no need to do REINDEX exclusively.
Question : Do we need to consider  Table dependencies while preparing
script in order to avoid table locks during vacuum full ?

At present Maintenance work memory set to 20 GB.
Question : Do we need to tweak any other parameters ?

Note:
We are planning this activity with Application Downtime only.

Let me know if i missed anything.

Regards,
Raj





On Wed, Apr 3, 2019 at 8:42 AM rihad  wrote:

> > 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 regularly, although we had to tweak it down not to interfere
> with normal database activity, so it takes several hours each run on the
> table. We did that by setting autovacuum_vacuum_scale_factor = 0.05 from
> default 0.2.
>
>
>

Re: Forcing index usage

[Michael Lewis]

>
> 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 keyword, it is an optimization boundary)
and then ordering and applying your limit to the materialized set that
comes out of that. eg.

WITH cte_full_text_results AS(
SELECT date, result FROM big_a_table WHERE text = 'whatever'
)
SELECT * FROM cte_full_text_results ORDER BY date DESC limit 10;

Re: PostgreSQL Windows 2019 support ?

[Tom Lane]

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 accepted:

https://www.postgresql.org/message-id/flat/CAJrrPGcfqXhfPyMrny9apoDU7M1t59dzVAvoJ9AeAh5BJi%2BUzA%40mail.gmail.com

If you want to help move things along, you could review/test the patch.

regards, tom lane

Re: Lingering replication slots

[Lou Tseng]

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
[http://advancedpricing.com/wp-content/uploads/logo_AMPS_email.png]
Advanced Medical Pricing Solutions
35 Technology Parkway South, Suite. 100
Peachtree Corners, GA 30092



From: Adrian Klaver 
Sent: Wednesday, April 3, 2019 11:27 AM
To: Lou Tseng; pgsql-gene...@postgresql.org
Subject: 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 slots that I can't delete
> because the active is true. Do I just kill the pid?
>
> Thanks!
>
>
> SELECT * FROM pg_replication_slots ;
>
> slot_name |plugin| slot_type |datoid|database| temporary | active |
> active_pid | xmin | catalog_xmin | restart_lsn| confirmed_flush_lsn
>
> --+--+---+--++---+++--+--+--+-
>
> support_sub2 | pgoutput | logical | 28384483 | dragon_support | f |
> t|29566 || 96562907 | 5108/29C1610 |
>
> support_sub| pgoutput | logical | 28384483 | dragon_support | f |
> t|27253 || 96562907 | 5108/2858880 |
>


See below(read full description at link):

https://www.postgresql.org/docs/11/functions-admin.html#FUNCTIONS-REPLICATION

pg_drop_replication_slot(slot_name name)


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: stale WAL files?

[Gmail]

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 sorted out which have 
been cleaned up but can do so if that’s thought to be helpful.

There is still 2.2G in the pg_wal directory but that disc has ~360G left. (I 
believe the burst of WAL files was the result of a novice using LIMIT with a 
to-Json function and the target table has >100M rows.)

Given that current WALs come and go regularly, I think the CHECKPOINT is 
running frequently enough (for normal loads at least).


> 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  wrote:
>>> > 
>>> > 
>>>  On Mar 29, 2019, at 6:58 AM, Michael Paquier  
>>>  wrote:
>>> >>> 
>>> >>> On Thu, Mar 28, 2019 at 09:53:16AM -0600, Rob Sargent wrote:
>>> >>> This is pg10 so it's pg_wal.  ls -ltr
>>> >>> 
>>> >>> 
>>> >>> -rw---. 1 postgres postgres 16777216 Mar 16 16:33
>>> >>> 00010CEA00B1
>>> >>> -rw---. 1 postgres postgres 16777216 Mar 16 16:33
>>> >>> 00010CEA00B2
>>> >>> 
>>> >>> ... 217 more on through to ...
>>> >>> 
>>> >>> -rw---. 1 postgres postgres 16777216 Mar 16 17:01
>>> >>> 00010CEA00E8
>>> >>> -rw---. 1 postgres postgres 16777216 Mar 16 17:01
>>> >>> 00010CEA00E9
>>> >>> -rw---. 1 postgres postgres 16777216 Mar 28 09:46
>>> >>> 00010CEA000E
>>> > I’m now down to 208 Mar 16 WAL files so they are being processed (at 
>>> > least deleted).  I’ve taken a snapshot of the pg_wal dir such that I can 
>>> > see which files get processed. It’s none of the files I’ve listed 
>>> > previously
>>> 
>>> Two more have been cleaned up.  001C and 001D generated at 16:38 Mar 16
>> Please share your complete postgresql.conf file and the results from this 
>> query: 
>> SELECT * FROM pg_settings;
>> has someone in the past configured wal archiving?
>> You've ran out of disk space as this log message you shared states:
>> No space left on device
>> what's the output of df -h
>> 
>> -- 
>> El genio es 1% inspiración y 99% transpiración.
>> Thomas Alva Edison
>> http://pglearn.blogspot.mx/
> 
> BTW , how spread apart are checkpoints happening? do you have stats on that? 
> maybe they're too spread apart and that's why WAL files cannot be recycled 
> rapidly enough?  
> -- 
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/
> 

Re: Lingering replication slots

[Adrian Klaver]

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 slots that I can't delete 
because the active is true. Do I just kill the pid?


Thanks!


SELECT * FROM pg_replication_slots ;

slot_name |plugin| slot_type |datoid|database| temporary | active | 
active_pid | xmin | catalog_xmin | restart_lsn| confirmed_flush_lsn


--+--+---+--++---+++--+--+--+-

support_sub2 | pgoutput | logical | 28384483 | dragon_support | f | 
t|29566 || 96562907 | 5108/29C1610 |


support_sub| pgoutput | logical | 28384483 | dragon_support | f | 
t|27253 || 96562907 | 5108/2858880 |





See below(read full description at link):

https://www.postgresql.org/docs/11/functions-admin.html#FUNCTIONS-REPLICATION

pg_drop_replication_slot(slot_name name)


--
Adrian Klaver
adrian.kla...@aklaver.com

PostgreSQL Windows 2019 support ?

[david moloney]

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

[Jonathan Marks]

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 results by a separate date column 
(which has an index) and we almost always use a limit.

Whenever the query planner chooses to use the indexes, queries on these tables 
are somewhat fast, maxing out at a few hundred milliseconds per query (which is 
terrible, but acceptable to end users).

When the query planner chooses not to use the indexes, queries can take many 
tens of seconds if they ever finish at all. When this happens, the query 
planner usually chooses to use the date index instead of the GIN index, and 
that is almost always a bad idea. We have sometimes been able to trick it into 
a better query plan by also adding the tsvector column in the ORDER BY clause, 
but that has bad performance implications if the result set is large.

Is there a way to tell Postgres “please don’t use index X when queries that 
could use index Y instead occur?”

Thank you!

Lingering replication slots

[Lou Tseng]

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 active is true. Do 
I just kill the pid?

Thanks!


SELECT * FROM pg_replication_slots ;


  slot_name   |  plugin  | slot_type |  datoid  |database| temporary | 
active | active_pid | xmin | catalog_xmin | restart_lsn  | confirmed_flush_lsn

--+--+---+--++---+++--+--+--+-

 support_sub2 | pgoutput | logical   | 28384483 | dragon_support | f | 
t  |  29566 |  | 96562907 | 5108/29C1610 |

 support_sub  | pgoutput | logical   | 28384483 | dragon_support | f | 
t  |  27253 |  | 96562907 | 5108/2858880 |

Re: Recommendation to run vacuum FULL in parallel

[rihad]

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 regularly, although we had to tweak it down not to interfere 
with normal database activity, so it takes several hours each run on the 
table. We did that by setting autovacuum_vacuum_scale_factor = 0.05 from 
default 0.2.

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

[Tom Lane]

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 probably out of luck if the table has a
>> TOAST table, unless the TOAST table is empty.  There wouldn't be
>> any good way to keep TOAST pointers valid across the move.

> Hmm, couldn't you use the binary-upgrade support functions just prior to
> creating the table in the target database?

Yeah, in theory you could do that, if the OID isn't already in use for
a table.  But that just added several more steps and more ways to
shoot yourself in the foot.  I'm starting to think this wouldn't really
be worth the risk.

regards, tom lane

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

[Alvaro Herrera]

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 more or less the same thing that
> > pg_upgrade --link does.
> 
> 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 probably out of luck if the table has a
> TOAST table, unless the TOAST table is empty.  There wouldn't be
> any good way to keep TOAST pointers valid across the move.

Hmm, couldn't you use the binary-upgrade support functions just prior to
creating the table in the target database?  If the OID is not already
being used in the target database, it should be possible to reserve it
just like pg_upgrade does it.  This is a one-shot for a single table, so
there's no need to automate it or anything too sophisticated.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Recommendation to run vacuum FULL in parallel

[Peter J. Holzer]

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 return it to the
> > OS."
> > 
> > Can you expound on that? I thought that was exactly what autovacuum did
> > for old versions of rows whether dead because of delete or update, so I
> > am surprised by this statement. I thought vacuum full was only ever
> > needed if storage space is an issue and the table is not expect to
> > quickly re-expand to current size on disk from new churn of tuples.
> 
> 
> From what I understand from the docs updates keep older versions of rows
> intact because other transactions might still use them (this is the essence
> of MVCC), and autovacuuming (plain VACUUM) marks that space as available
> when it is run, so future inserts can reuse it.

And future updates can reuse it, too (an update is very similar to an
insert+delete).

> In case the number of updates is much greater than the number of
> inserts, the unused zombie space gradually creeps up.

Not if autovacuum has a chance to run between updates.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

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

[Tom Lane]

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 --link does.

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 probably out of luck if the table has a
TOAST table, unless the TOAST table is empty.  There wouldn't be
any good way to keep TOAST pointers valid across the move.

(PG v12 will make that easier, but that's no help to you today.)

regards, tom lane

Re: Recommendation to run vacuum FULL in parallel

[rihad]

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 that was exactly what autovacuum 
did for old versions of rows whether dead because of delete or update, 
so I am surprised by this statement. I thought vacuum full was only 
ever needed if storage space is an issue and the table is not expect 
to quickly re-expand to current size on disk from new churn of tuples.



From what I understand from the docs updates keep older versions of 
rows intact because other transactions might still use them (this is the 
essence of MVCC), and autovacuuming (plain VACUUM) marks that space as 
available when it is run, so future inserts can reuse it. In case the 
number of updates is much greater than the number of inserts, the unused 
zombie space gradually creeps up.

Re: Seeded Replication

[Lou Tseng]

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
ALTER SUBSCRIPTION ... ENABLE

--> errors occur here

Thoughts?


Lou Tseng


lts...@advancedpricing.com
[http://advancedpricing.com/wp-content/uploads/logo_AMPS_email.png]
Advanced Medical Pricing Solutions
35 Technology Parkway South, Suite. 100
Peachtree Corners, GA 30092



From: Pavan Teja 
Sent: Wednesday, April 3, 2019 6:48 AM
To: Lou Tseng
Cc: pgsql-gene...@postgresql.org >> PG-General Mailing List
Subject: 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 approach.

Kindly revert back if any queries.

Regards,
Pavan Teja,
9841380956

On Wed, 3 Apr, 2019, 5:15 PM Lou Tseng, 
mailto:lts...@advancedpricing.com>> 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 subscriber database with data dump and then turn on the 
subscription like depesz showed in this post 
https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-logical-replication/
 .  It works for the small testing database but when I applied same steps with 
entire database, I am getting "ERROR:  duplicate key value violates unique 
constraint" errors. Basically postgresql does not recognize the primary key.

Any thought / suggestion is helpful!


Lou Tseng


lts...@advancedpricing.com
[http://advancedpricing.com/wp-content/uploads/logo_AMPS_email.png]
Advanced Medical Pricing Solutions
35 Technology Parkway South, Suite. 100
Peachtree Corners, GA 30092

Re: Recommendation to run vacuum FULL in parallel

[Michael Lewis]

"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 versions of rows whether dead because of delete or update, so I am
surprised by this statement. I thought vacuum full was only ever needed if
storage space is an issue and the table is not expect to quickly re-expand
to current size on disk from new churn of tuples.

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

[Adrian Klaver]

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 + restore...

Thanks





--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Steven Lembark]

> 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
lemb...@wrkhors.com+1 888 359 3508

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

[Ron]

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 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 tablespace in common, is there any way to move
the contents without a dump & reload?

COPY TO STDIN and pipe that through ssh to another server?

That makes a copy. There is no place to store two copies of the
full data on disk.


Through a pipe to a different server.


I'm basically trying to perform the moral equivalent of what "mv"
does in the filesystem: re-link the inode and remove the old inode
without duplicating the data.

Since the tablespace is visible to both I would get that there is
some way to alter table... that allocates the space on disk to the
new database w/o having to unload all 400M rows -- if it comes down
to that I'll have to copy it into xz and load it back in the new
database.



--
Angular momentum makes the world go 'round.

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

[Tom Lane]

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 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 --link does.  But doing it by hand is not officially supported
and there are multiple ways to shoot yourself in the foot.  Basically
the idea is to create an identically-declared table in the target
database, and then swap the physical files of the two tables.  Read

https://www.postgresql.org/docs/current/storage.html

--- *carefully* --- to find out how to identify the right physical
files.

A few foot-guns I can think of:

* Making an identically-declared table might be more complicated than
you'd think, if the table has had any ALTERs done to its rowtype over
its lifetime (ALTER DROP COLUMN is a particularly critical bit of
history here).  A good way to proceed is to see what
"pg_dump -s --binary_upgrade" does to recreate the table.

* Shut down the postmaster while doing the actual file movement,
else you'll get burnt by cached page copies.

* Don't forget to move all the associated files, including multiple
segment files (I'm sure you have a lot, if this table is big enough
to be worth troubling over), and FSM and VM files.

* The indexes on the table also need to be moved through the same
type of process.

I'd strongly counsel practicing on a test setup before you try to
do this to your live data.

Oh: and you have a backup, I trust.

regards, tom lane

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

[Steven Lembark]

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 an 80% full disk.
> >
> > 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?  
> 
> COPY TO STDIN and pipe that through ssh to another server?

That makes a copy. There is no place to store two copies of the 
full data on disk. 

I'm basically trying to perform the moral equivalent of what "mv"
does in the filesystem: re-link the inode and remove the old inode
without duplicating the data.

Since the tablespace is visible to both I would get that there is
some way to alter table... that allocates the space on disk to the
new database w/o having to unload all 400M rows -- if it comes down
to that I'll have to copy it into xz and load it back in the new
database.

-- 
Steven Lembark 3920 10th Ave South
Workhorse Computing   Birmingham, AL 35222
lemb...@wrkhors.com+1 888 359 3508

Sv: SQL queries not matching on certain fields

[Andreas Joseph Krogh]

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 that works as expected:
 # SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername"='emma'; 
id | preferredUsername ---+--- 48952 | emma 58672 | emma (2 
rows) 
The following query should work as well, because the username exists. But in 
fact, it consistently returns nothing:
 # SELECT id, "preferredUsername" FROM actor WHERE 
"preferredUsername"='mailab'; id | preferredUsername +--- 
(0 rows) 
There are some workarounds which fix the WHERE statement, all of the following 
work as expected:
SELECT id, "preferredUsername" FROM actor WHERE trim("preferredUsername")=
'mailab'; SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername" 
ILIKE'mailab'; SELECT id, "preferredUsername" FROM actor WHERE md5(
"preferredUsername")=md5('mailab'); 


Now you might think that there is something wrong with the encoding, or the 
field contains some extra whitespace. But SHOW SERVER_ENCODING and SHOW 
SERVER_ENCODING show UTF8. Length and m5 sum are also exactly as expected. And 
I checked the individual bytes with get_byte(), all of them are in the range 
97-122.

About the setup, this is Postgres 10 running in Docker, on an Ubuntu VPS (see 
below for all versions etc). I had this problem before on the same setup, so I 
did an export to text file with pg_dump, and imported into a completely new 
database with psql. That fixed the problem for a few days, but it came back 
soon after.

The problem only seems to affect one or two specific columns, and only a few 
specific rows in those columns. Most other rows work normally. Affected columns 
also randomly start working again after a few days, and other columns get 
affected. I havent noticed any kind of pattern.

You can find the table definition here: https://gitlab.com/snippets/1840320 


Version info:

Postgres Docker Image: postgres:10.7-alpine
 Docker version: 18.09.2
 OS: Ubuntu 18.04.2

Please tell me if you have any idea how to fix or debug this. I already asked 
multiple people, and no one has a clue what is going on.

Best,
 Felix Ableitner
Does disabling index-scan make a difference? SET enable_indexscan to off;
How about dumping the relevant data and reloading it into another similar (but 
smaller) table, can you reproduce it then? -- Andreas Joseph Krogh CTO / Partner
 - Visena AS Mobile: +47 909 56 963 andr...@visena.com 
 www.visena.com   

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

[Ron]

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 cluster and have
the owner & the tablespace in common, is there any way to move
the contents without a dump & reload?


COPY TO STDIN and pipe that through ssh to another server?

--
Angular momentum makes the world go 'round.

Re: SQL queries not matching on certain fields

[Torsten Förtsch]

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 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 "preferredUsername"='emma';
>   id   | preferredUsername
> ---+---
>  48952 | emma
>  58672 | emma
> (2 rows)
>
> The following query should work as well, because the username exists. But
> in fact, it consistently returns nothing:
>
> # SELECT id, "preferredUsername" FROM actor WHERE 
> "preferredUsername"='mailab';
>  id | preferredUsername
> +---
>
>   (0 rows)
>
> There are some workarounds which fix the WHERE statement, all of the
> following work as expected:
>
> SELECT id, "preferredUsername" FROM actor WHERE 
> trim("preferredUsername")='mailab';
> SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername" ILIKE 
> 'mailab'; SELECT id, "preferredUsername" FROM actor WHERE 
> md5("preferredUsername")=md5('mailab');
>
>
> Now you might think that there is something wrong with the encoding, or
> the field contains some extra whitespace. But SHOW SERVER_ENCODING and SHOW
> SERVER_ENCODING show UTF8. Length and m5 sum are also exactly as expected.
> And I checked the individual bytes with get_byte(), all of them are in the
> range 97-122.
>
> About the setup, this is Postgres 10 running in Docker, on an Ubuntu VPS
> (see below for all versions etc). I had this problem before on the same
> setup, so I did an export to text file with pg_dump, and imported into a
> completely new database with psql. That fixed the problem for a few days,
> but it came back soon after.
>
> The problem only seems to affect one or two specific columns, and only a
> few specific rows in those columns. Most other rows work normally. Affected
> columns also randomly start working again after a few days, and other
> columns get affected. I havent noticed any kind of pattern.
>
> You can find the table definition here:
> https://gitlab.com/snippets/1840320
>
> Version info:
>
> Postgres Docker Image: postgres:10.7-alpine
> Docker version: 18.09.2
> OS: Ubuntu 18.04.2
>
> Please tell me if you have any idea how to fix or debug this. I already
> asked multiple people, and no one has a clue what is going on.
>
> Best,
> Felix Ableitner
>

Re: SQL queries not matching on certain fields

[Steve Atkins]

> 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 expected:
> 
> # SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername"='emma';
>   id   | preferredUsername
> ---+---
>  48952 | emma
>  58672 | emma
> (2 rows)
> 
> The following query should work as well, because the username exists. But in 
> fact, it consistently returns nothing:
> 
> # SELECT id, "preferredUsername" FROM actor WHERE 
> "preferredUsername"='mailab';
>  id | preferredUsername
> +---
> 
>   (0 rows)
> 
> There are some workarounds which fix the WHERE statement, all of the 
> following work as expected:
> 
> SELECT id, "preferredUsername" FROM actor WHERE 
> trim("preferredUsername")='mailab';
> 
> 
> 
> SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername" ILIKE 
> 'mailab'; 
> 
> SELECT id, "preferredUsername" FROM actor WHERE 
> md5("preferredUsername")=md5('mailab'); 
> 
> 
> Now you might think that there is something wrong with the encoding, or the 
> field contains some extra whitespace. But SHOW SERVER_ENCODING and SHOW 
> SERVER_ENCODING show UTF8. Length and m5 sum are also exactly as expected. 
> And I checked the individual bytes with get_byte(), all of them are in the 
> range 97-122.
> 
> About the setup, this is Postgres 10 running in Docker, on an Ubuntu VPS (see 
> below for all versions etc). I had this problem before on the same setup, so 
> I did an export to text file with pg_dump, and imported into a completely new 
> database with psql. That fixed the problem for a few days, but it came back 
> soon after.
> 
> The problem only seems to affect one or two specific columns, and only a few 
> specific rows in those columns. Most other rows work normally. Affected 
> columns also randomly start working again after a few days, and other columns 
> get affected. I havent noticed any kind of pattern.
> 
> You can find the table definition here: https://gitlab.com/snippets/1840320

You can use "explain" to see what plan is being used for the query, but I'm 
betting that it's using an index on preferredUsername. Your workarounds won't 
use that index, they'll scan the table.

If that index is corrupted it could cause the symptoms you're seeing. You can 
use "reindex" to rebuild the index from scratch and see if it fixes it but 
corrupted indexes aren't normal, and the issue seems to be recurring. On 
physical hardware I'd be wondering about filesystem corruption and taking a 
good look at my system logs. On someone else's VPS you don't have the same 
visibility, but I'd still check the system logs for issues.

Cheers,
  Steve

> 
> Version info:
> 
> Postgres Docker Image: postgres:10.7-alpine 
> Docker version: 18.09.2
> OS: Ubuntu 18.04.2
> 
> Please tell me if you have any idea how to fix or debug this. I already asked 
> multiple people, and no one has a clue what is going on.
> 
> Best,
> Felix Ableitner
> 

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

[Steven Lembark]

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 tablespace in common, is there any way to move
the contents without a dump & reload?

Thanks

-- 
Steven Lembark 3920 10th Ave South
Workhorse Computing   Birmingham, AL 35222
lemb...@wrkhors.com+1 888 359 3508

SQL queries not matching on certain fields

[Felix Ableitner]

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 "preferredUsername"='emma';
  id   | preferredUsername
---+---
 48952 | emma
 58672 | emma
(2 rows)

The following query should work as well, because the username exists.
But in fact, it consistently returns nothing:

# SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername"='mailab';
 id | preferredUsername
+---

  (0 rows)

There are some workarounds which fix the WHERE statement, all of the
following work as expected:

|SELECTid, "preferredUsername"FROMactor
WHEREtrim("preferredUsername")='mailab';||SELECTid, 
"preferredUsername"FROMactor WHERE"preferredUsername"ILIKE
'mailab';|
|||SELECTid, "preferredUsername"FROMactor
WHEREmd5("preferredUsername")=md5('mailab');|
||


Now you might think that there is something wrong with the encoding, or
the field contains some extra whitespace. But SHOW SERVER_ENCODING and
SHOW SERVER_ENCODING show UTF8. Length and m5 sum are also exactly as
expected. And I checked the individual bytes with get_byte(), all of
them are in the range 97-122.

About the setup, this is Postgres 10 running in Docker, on an Ubuntu VPS
(see below for all versions etc). I had this problem before on the same
setup, so I did an export to text file with pg_dump, and imported into a
completely new database with psql. That fixed the problem for a few
days, but it came back soon after.

The problem only seems to affect one or two specific columns, and only a
few specific rows in those columns. Most other rows work normally.
Affected columns also randomly start working again after a few days, and
other columns get affected. I havent noticed any kind of pattern.

You can find the table definition here: https://gitlab.com/snippets/1840320

Version info:

Postgres Docker Image: postgres:10.7-alpine
Docker version: 18.09.2
OS: Ubuntu 18.04.2

Please tell me if you have any idea how to fix or debug this. I already
asked multiple people, and no one has a clue what is going on.

Best,
Felix Ableitner

Re: Seeded Replication

[Pavan Teja]

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 approach.

Kindly revert back if any queries.

Regards,
Pavan Teja,
9841380956

On Wed, 3 Apr, 2019, 5:15 PM 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 subscriber database with data dump and
> then turn on the subscription like depesz showed in this post
> https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-logical-replication/
>  .
> It works for the small testing database but when I applied same steps with
> entire database, I am getting "ERROR:  duplicate key value violates unique
> constraint" errors. Basically postgresql does not recognize the primary key.
>
> Any thought / suggestion is helpful!
>
> Lou Tseng
>
> lts...@advancedpricing.com
> 
> Advanced Medical Pricing Solutions 
> 35 Technology Parkway South, Suite. 100
> Peachtree Corners, GA 30092
>
>

Seeded Replication

[Lou Tseng]

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 then turn on the 
subscription like depesz showed in this post 
https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-logical-replication/
 .  It works for the small testing database but when I applied same steps with 
entire database, I am getting "ERROR:  duplicate key value violates unique 
constraint" errors. Basically postgresql does not recognize the primary key.

Any thought / suggestion is helpful!


Lou Tseng


lts...@advancedpricing.com
[http://advancedpricing.com/wp-content/uploads/logo_AMPS_email.png]
Advanced Medical Pricing Solutions
35 Technology Parkway South, Suite. 100
Peachtree Corners, GA 30092

Re: Recommendation to run vacuum FULL in parallel

[Peter J. Holzer]

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) pg_repack and pg_squeeze. It would be nice to have
that in the core, though.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Reg: Pg_Ctl command help

[Nadeem Akbar basha]

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 command prompt. But as soon as I 
close the console, the server gets forcefully shutdown. Again I have to restart 
the server  through command prompt.
Is there any way, where after starting the server, I have to exit the command 
prompt, still running the server at the background.

Please help me to resolve this issue.

Thanks & Regards,
A. Nadeem Ahmed

::DISCLAIMER::
--
The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only. E-mail transmission is not guaranteed to be 
secure or error-free as information could be intercepted, corrupted, lost, 
destroyed, arrive late or incomplete, or may contain viruses in transmission. 
The e mail and its contents (with or without referred errors) shall therefore 
not attach any liability on the originator or HCL or its affiliates. Views or 
opinions, if any, presented in this email are solely those of the author and 
may not necessarily reflect the views or opinions of HCL or its affiliates. Any 
form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of this message without the prior written 
consent of authorized representative of HCL is strictly prohibited. If you have 
received this email in error please delete it and notify the sender 
immediately. Before opening any email and/or attachments, please check them for 
viruses and other defects.
--

Re: Recommendation to run vacuum FULL in parallel

[rihad]

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 and more unused space that is never 
used again by postgres, and plain autovacuuming doesn't return it to the 
OS. So DB size (as witnessed by psql's \l+) uses 5-6x times the space it 
actually needs. And using vacuum full is prohibitive because of the 
exclusive lock it takes on the table, preventing both writes and reads. 
Since rewriting a table is a completely internal operation from clients' 
POV, hopefully one day we will see a concurrent version of vacuum full.

Re: New LLVM JIT Features

[preejackie]

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 00:51:51 +0530, preejackie wrote:

As LLVM ORC supports compiling in multiple backend threads, it would be
effective if we compile the functions speculatively before they are called
by the executing function. So when we request JIT to compile a function, JIT
will immediately returns the function address for raw executable bits. This
will greatly reduce the JIT latencies in modern multi-core machines.

I personally think this should be approached somewhat differently -
putting patchpoints into code reduces the efficiency of the generated
code, so I don't think that's the right approach. What I think we should

  What do you mean by patch points here? To my knowledge, LLVM symbols have
arbitrary stub associated which resolve to function address at function
address.

I was assuming that you'd want to improve latency by not compiling all
the functions at the start of the executor (like we currently do), but
have sub-functions compiled in the background. That'd require
patchpoints to be able to initially redirect to a function to wait for
compilation, which then can be changed to directly jump to the function.
Because we already just compile all the functions reachable at the start
of execution in one go, so it's not a one-by-one function affair.

  Compiling the whole module will increase your start-up time of the 
application right? Is there any techniques applied in Pgsql to handle 
this ? Sometimes, you will compile functions that you don't need 
immediately or even it will not called in run time. This is the 
trade-off between different JIT implementations.  Also adding patch 
points in the generated code will degrade performance only when we 
didn't compile the function ahead-of-time, theoretically this will patch 
points miss will go down when we increase the number of compiler 
threads. And practically every computer have at least 4 cores nowadays.



do is to, if we decide it's worthwhile at plan time, generate the LLVM
IR time at the beginning of execution, but continue to use interpreted
execution initially. The generated IR would then be handed over to a
background [process|thread|whatnot] for optimization of code
generation. Then, when finished, I'd switch over from interpreted to JIT
compiled execution.  That approach will, in my view, yield better
latency behaviour because we can actually evaluate quals etc for which
we've not yet finished code generation.



And also I'm working on designing a ORC in-place dynamic profiling support, by
this JIT will automatically able to identify the hot functions, and compile
it in higher optimization level to achieve good performance.

I think that's a nice concept, but at the moment the generated code is
so bad that it's much more likely to get big benefits by improving the
generated IR, compared to giving more hints to the optimizer.

By improving the generated IR, you mean by turning pgsql queries into LLVM
IR? If it is the case, this design doesn't handles that, it works only when
the given program representation is in LLVM IR.

My point is that we generate IR that's hard for LLVM to optimize. And
that fixing that is going to give you way bigger wins than profile
guided optimization.
  I hope this is problem of Pgsql, but I'm proposing this project for 
LLVM Community.


Greetings,

Andres Freund


--
Have a great day!
PreeJackie