Re: Warning of .partial wal file in PITR and Replication Environment

[Michael Paquier]

On Mon, Mar 26, 2018 at 11:52:53AM +, Amee Sankhesara - Quipment India 
wrote:
> Warning : The failed archive command was: copy
> "D:\PostgreSQL\9.6\data\pg_xlog\00010A8800F8.partial"
> "\\10.0.0.35\Archive_Replication\00010A8800F8.partial" |
> copy "D:\PostgreSQL\9.6\data\pg_xlog\00010A8800F8.partial"
> "\\10.0.0.35\Archive_PITR\00010A8800F8.partial"

Am I reading that correctly or you are trying to copy twice the same
file?  Why?  Using only copy may cause corruptions if you are not
careful as a disabled write caching would cause the data to go to the OS
cache on Windows and not directly to disk.

> Due to this it has stopped to take backup of PITR
> 
> So we like to know how to clean up the ".partial" file from pg_xlog and run 
> PITR  backup smoothly
> Also like to know is there any database consistency related issue ?

This is the last, partial WAL segment from the previous timeline.
Normally such things are able to archive correctly, so you may want to
consider a more advanced archive command able to handle duplicates.
It is impossible to give a strict answer before knowing what you are
looking for in terms of WAL archive redundancy.

You may need to use target_timeline = 'latest' in your recovery.conf
settings as well.
--
Michael


signature.asc
Description: PGP signature

Re: Problem with postgreSQL

[Adrian Klaver]

On 03/26/2018 04:41 AM, Gian mario Caggiu wrote:
Hi, I'm a student and I downloaded postgreSQL to my Mac but I found a 
problem. The program does not start, I do not understand why, but that's 


Where did you download it from?

What version of Postgres?

How did you install it?

not what scares me. The fact is that a virtual user has been created 
called 'postgreSQL' which has appropriated all the functions of the 
administrator user. Can I kindly ask you why and how did this happen?



Gian Mario Caggiu



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

Re: [GENERAL] missing public on schema public

[Michael Paquier]

On Mon, Mar 26, 2018 at 12:46:38PM -0300, Alvaro Herrera wrote:
> Was this ever fixed?

Ugh.  I have added a reminder on the open item page for v11 as an older
bug: 
https://wiki.postgresql.org/wiki/PostgreSQL_11_Open_Items#Older_Bugs
--
Michael


signature.asc
Description: PGP signature

Re: PostgreSQL version on VMware vSphere ESXI 6.5 and harware version 13

[Michael Paquier]

On Mon, Mar 26, 2018 at 01:49:30PM -0700, David G. Johnston wrote:
> You should probably ask this question on the VMWare forums since its their
> software that would or would not have the performance improvements.
> PostgreSQL will benefit from any memory and disk-related virtualization
> enhancements that they made.  I would have to assume that the answer is a
> favorable yes.

(VMware human here).

Upgrading ESX or other VMware-related components has nothing to do with
PostgreSQL.  Some of the products may embed a version of PostgreSQL to
store some of their meta-data or other things, in which case the upgrade
to a newer PostgreSQL version, if need be, will be taken care of by the
product itself.  If you are using your own set of PostgreSQL instances,
then that's up to what you have at OS level.
--
Michael


signature.asc
Description: PGP signature

Re: PostgreSQL version on VMware vSphere ESXI 6.5 and harware version 13

[David G. Johnston]

On Mon, Mar 26, 2018 at 1:28 PM, nmmulla  wrote:

> Thanks JD.
>
> Like i said we are not changing OS at all. WE are just upgrading VMWare
> from
> ESXi 5.5 to 6.5 and hardware from version 10 to 13.
>
> We are using Linux 6, 7.
>
> By just upgrading VMWare will it affects the performane of postgres
> databases?


You should probably ask this question on the VMWare forums since its their
software that would or would not have the performance improvements.
PostgreSQL will benefit from any memory and disk-related virtualization
enhancements that they made.  I would have to assume that the answer is a
favorable yes.

David J.

Re: PostgreSQL version on VMware vSphere ESXI 6.5 and harware version 13

[nmmulla]

Thanks JD.

Like i said we are not changing OS at all. WE are just upgrading VMWare from
ESXi 5.5 to 6.5 and hardware from version 10 to 13.

We are using Linux 6, 7.

By just upgrading VMWare will it affects the performane of postgres
databases?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: PostgreSQL version on VMware vSphere ESXI 6.5 and harware version 13

[Joshua D. Drake]

On 03/26/2018 07:03 AM, nmmulla wrote:

We are upgrading Vmware to version 6.5.

Operating system for virtual servers will not be changing. The other change
that will be happening is VM hardware version which will be upgraded from
current version ( 9 or 10) to version 13.


Your compatibility is based on the OS, not VMWare. As long as you are 
using a PostgreSQL compatible OS you are fine.


JD
--

Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Query performance

[hmidi slim]

I want to compare the performance of some queries so I created those tables:


*CREATE TABLE reservation1 (room int, name varchar(255), during
daterange);CREATE TABLE reservation2 (room int, name varchar(255),
start_date date, end_date date);*

then:




*INSERT INTO public.reservation1(name, room, during) select
concat('room ', a), a, '[2010-01-01, 2010-04-02)' from
generate_series(1,1000) as a;INSERT INTO public.reservation2(name,
room, start_date, end_date) select concat('room ', a), a, '2010-01-01',
'2010-04-02' from generate_series(1,1000) as a;*

I create an index for during column:


*CREATE INDEX reservation1_idx ON reservation1 USING GIST (during);*
I'm using the operator contains (@>) and overlaps (&&):






*select * from reservation1where during @>
'[2010-02-15,2010-03-02)';select * from reservation1where during &&
'[2010-02-15,2010-03-02)'; *
And I got such result time:

* 25s*
However when I use this query:




*select * from reservation2where ('2010-02-15' between start_date and
end_date)and ('2010-03-02' between start_date and end_date);*
The result time was:

* 9s.*
I understand that the index does not have any effect when the amount of
fetched data was huge and the query planner used the seq scan method. I
want to know if it is not recommended to use rang types and operator to get
good performance or should I add something to the queries to be faster?

Re: [GENERAL] missing public on schema public

[Alvaro Herrera]

Was this ever fixed?


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

Re: PostgreSQL version on VMware vSphere ESXI 6.5 and harware version 13

[nmmulla]

We are upgrading Vmware to version 6.5. 

Operating system for virtual servers will not be changing. The other change
that will be happening is VM hardware version which will be upgraded from
current version ( 9 or 10) to version 13.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

PostgreSQL version on VMware vSphere ESXI 6.5 and harware version 13

[nmmulla]

Hi,
We are upgrading our VMware version from 5.5 to 6.5.

Is PostgreSQL version 9.1 to 10.1 compatible on VMware vSphere ESXI 6.5 and
harware version 13.





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

RE: Foreign Key locking / deadlock issue.... v2

[HORDER Phil]

psql> select version();
PostgreSQL 9.6.1, compiled by Visual C++ build 1800, 64-bit