[GENERAL] How to upgrade PostgreSQL minor version?

2017-04-26 Thread Ron Ben 
I'm runing PostgreSQL 9.3.3 and I want to upgrade it to the latest 9.3 version
The documontation does not specify what needs to be done other than "just install the executables".
This is wierd as for example 9.3.5 release notes request to run a specifc query to  check for pg_multixact files left.
 
I saw this question:
https://serverfault.com/questions/563667/update-9-3-x-minor-verison-of-postgres-on-ubuntu-12-04
I'm not sure how this will handle the 9.3.5 check issue.
 
Is it really just runing one command?
What if there are errors and problems? how do i revert back?
Sadly there is not enough information regarding minor verion updates...
 
I'm also runing ubuntu server 12 which will be updated as well later this quarter.

Re: [GENERAL] tuple statistics update

2017-04-26 Thread Tom DalPozzo 
Hi,

2017-04-19 15:49 GMT+02:00 Adrian Klaver :

> On 04/19/2017 12:28 AM, Tom DalPozzo wrote:
>
>> 2017-04-18 21:42 GMT+02:00 Adrian Klaver > >:
>>
>> On 04/17/2017 09:18 AM, Tom DalPozzo wrote:
>>
>> Hi, I'm using libpq to insert tuples in my table and keep looking
>> at
>> statistics through psql instead.
>> I noticed that sometimes n_tuple_ins is not updated even after 1
>> min
>> that my transaction committed.
>> My libpq connection is kept alive. If I close the connection
>> then the
>> stats get updated.
>> I know that stats are not instantaneous, but I thought that after
>> a
>> while that a transaction is committed it would be updated.
>>
>>
>> Any of this apply?:
>>
>> https://www.postgresql.org/docs/9.6/static/monitoring-stats.html
>> 
>>
>> "Another important point is that when a server process is asked to
>> display any of these statistics, it first fetches the most recent
>> report emitted by the collector process and then continues to use
>> this snapshot for all statistical views and functions until the end
>> of its current transaction. So the statistics will show static
>> information as long as you continue the current transaction.
>> Similarly, information about the current queries of all sessions is
>> collected when any such information is first requested within a
>> transaction, and the same information will be displayed throughout
>> the transaction. This is a feature, not a bug, because it allows you
>> to perform several queries on the statistics and correlate the
>> results without worrying that the numbers are changing underneath
>> you. But if you want to see new results with each query, be sure to
>> do the queries outside any transaction block. Alternatively, you can
>> invoke pg_stat_clear_snapshot(), which will discard the current
>> transaction's statistics snapshot (if any). The next use of
>> statistical information will cause a new snapshot to be fetched."
>>
>> Regards
>> Pupillo
>>
>>
>> I read it, it seems to say that after N millisec that my transaction
>> ends,  stat should be current. I also tried pg_stat_clear_snapshot()
>> with no success.
>>
>
> You have two sessions in play, one that is inserting rows, the other in
> psql looking at the stats. It is not clear to me which session you are
> referring to in the above. So maybe an outline of what you are doing.
> Something like:
>
> Session 1 Monitor stats table(?) using command(?)
>
> Session2 Insert rows. The INSERT query
>


I have a psql session open.
Now, through another task which uses libpq, I open a new connection and
send these commands via PQexec:
CREATE TABLE stato (ID BIGINT,DATI BYTEA);
CREATE INDEX stato_IDX ON stato (ID);
INSERT INTO stato VALUES (0,'\x6C72B55EA171DE63F229A37135CB5DE4A845FD9E');
INSERT INTO stato VALUES (1,'\x9822A5A113EE5FBBA03C6B58A139DD46D4476B8D');

As it's done, I send the following commands via psql session, waiting at
least 1 sec before each one:

ginopino=# select count(*) from stato;
 count
---
 2
(1 row)

ginopino=# select relname,n_tup_ins,n_tup_upd  from pg_stat_user_tables
where relname='stato';
 relname | n_tup_ins | n_tup_upd
-+---+---
 stato   | 0 | 0
(1 row)

Repeat just in case
ginopino=# select count(*) from stato;
 count
---
 2
(1 row)

ginopino=# select relname,n_tup_ins,n_tup_upd  from pg_stat_user_tables
where relname='stato';
 relname | n_tup_ins | n_tup_upd
-+---+---
 stato   | 0 | 0
(1 row)

n_tup_ins still 0 while count is 2.

Now, I terminate my libpq task and then, from psql:
ginopino=# select relname,n_tup_ins,n_tup_upd  from pg_stat_user_tables
where relname='stato';
 relname | n_tup_ins | n_tup_upd
-+---+---
 stato   | 2 | 0
(1 row)

Now n_tup_ins is 2.

Thanks
Pupillo


>
> etc
>
>> Regards
>> Pupillo
>>
>>
>>
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] tuple statistics update

2017-04-26 Thread Tom DalPozzo 
Hi,

2017-04-19 16:37 GMT+02:00 Tom Lane :

> Tom DalPozzo  writes:
> > 2017-04-18 22:06 GMT+02:00 Tom Lane :
> >> If your session is just sitting, that's not surprising.  I think stats
> >> updates are only transmitted to the collector at transaction end (and
> >> even then, only if it's been at least N msec since the last transmission
> >> from the current session).
>
> > Hi, my transaction ended, that I waited 1 min and queried the stat.
> > I had to wait for session end in order to see the updeted stats.
> > Did you mean "...to the collector at SESSION end"?
>
> No, I meant what I said.  No amount of just-sitting will cause a session
> to do something: it either transmits stats at transaction end or it
> doesn't because it thinks it's too soon since its last update.  If you
> want to force out the stats, you could wait a second or so and then
> do a dummy transaction (even "select 1" will do, in testing).
>
> BTW, a quick look at the code says the threshold is 500 msec, ie,
> stats are sent only if it's been at least half a second since the
> session's last report.
>

I tried with a dummy select with no success. I detailed my test in my
answer to Adrian Klaver.
I'll avoid to duplicate it here.
Regards
Pupillo




>
> regards, tom lane
>

Re: [GENERAL] [OT] Help: stories of database security and privacy

2017-04-26 Thread Lifepillar 

On 12/04/2017 10:57, vinny wrote:

On 2017-04-12 09:09, Lifepillar wrote:

So, I am here to ask if you have
interesting/(in)famous stories to share on database security/privacy
"gone wrong" or "done right"(tm), possibly with technical details


One case that I remember from an ancient version of the book "hacking
exposed"
was about a MySQL server that was running under the root user. A badly
written
application allowed some SQL injection that let a hacker issue a SELECT
INTO OUTFILE
query that "selected" a bash script into the .login file of the root user,
and the next time the root user logged in, the script would create a new
superuser account
for the hacker.


After tweaking MySQL to be really insecure by unsetting
secure_file_prev, using grant file, etc..., I am indeed able to write
anywhere where the user running MySQL is able to. This, combined with
a trivial SQL injection vulnerability in a popular web application,
makes (I think) an interesting and easy to explain example of how one
might take over a system or an account.

Correct me if I am wrong, in PostgreSQL something similar can be
achieved using lo_export(), although you must connect as a superuser to
do that (while in MySQL you may grant file system access to any user).


I remember this particular example mainly because of the way that people
I told it to reacted;
some were of the opinion that the application was at fault for allowing
injection,
some thought the DBA was to blame for running as root,
but the vast majority did not know that MySQL could write files, let
alone overwrite system files.


Good point.

Thanks!
Life.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [OT] Help: stories of database security and privacy

2017-04-26 Thread vinny 

On 2017-04-26 11:47, Lifepillar wrote:

On 12/04/2017 10:57, vinny wrote:

On 2017-04-12 09:09, Lifepillar wrote:

So, I am here to ask if you have
interesting/(in)famous stories to share on database security/privacy
"gone wrong" or "done right"(tm), possibly with technical details


One case that I remember from an ancient version of the book "hacking
exposed"
was about a MySQL server that was running under the root user. A badly
written
application allowed some SQL injection that let a hacker issue a 
SELECT

INTO OUTFILE
query that "selected" a bash script into the .login file of the root 
user,
and the next time the root user logged in, the script would create a 
new

superuser account
for the hacker.


After tweaking MySQL to be really insecure by unsetting
secure_file_prev, using grant file, etc..., I am indeed able to write


MySQL used to be "really insecure", I'm glad to see they have taken 
measures

to prevent this attack. (now let's just hope that you cannot use SQL
to change tose security settings :-)



Correct me if I am wrong, in PostgreSQL something similar can be
achieved using lo_export(), although you must connect as a superuser to
do that (while in MySQL you may grant file system access to any user).


Technically, yes, but you cannot supply a path as easily as in MySQL.

The moral of the story is not so much that MySQL is unsafe, but that 
attacks
can come from the most unexpected places. Even from things you did not 
even know

to be possible. Again: if something sis not required to be possible,
then measures should be taken to make it impossible.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to upgrade PostgreSQL minor version?

2017-04-26 Thread Sameer Kumar 
On Wed, Apr 26, 2017 at 4:41 PM Ron Ben  wrote:

> I'm runing PostgreSQL 9.3.3 and I want to upgrade it to the latest 9.3
> version
> The documontation does not specify what needs to be done other than "just
> install the executables".
> This is wierd as for example 9.3.5 release notes request to run a specifc
> query to  check for pg_multixact files left.
>
> I saw this question:
>
> https://serverfault.com/questions/563667/update-9-3-x-minor-verison-of-postgres-on-ubuntu-12-04
> I'm not sure how this will handle the 9.3.5 check issue.
>
>

What is the version of PostgreSQL that you are running?

Though there are some general guidelines/procedure (which is as simple as
stop the server, upgrade binaries and start, there will also be some
peculiar releases which has a bug-fix. While the bug-fix covers you in
future, there might have been incidents where you are already bitten by bug
being fixed (worse if you have not yet identified it). To cover such cases,
you would need to follow some custom procedure. Again, mostly the custom
steps are not for patch application but more of a mitigation against risks
of bug or in some cases bug-fix.

Are you running with a standby or just stand-alone setup?


Is it really just runing one command?
> What if there are errors and problems? how do i revert back?
> Sadly there is not enough information regarding minor verion updates...
>
> I'm also runing ubuntu server 12 which will be updated as well later this
> quarter.
>

Do you have a test environment? If I were you I would have a non-prod that
mimics my production setup exactly and I would apply the patch there first.


-- 

-- 

Best Regards,

*Sameer Kumar | Senior Solution Architect*

*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

T: +65 6438 3504 | www.ashnik.com

Skype: sameer.ashnik |   M: +65 8110 0350

Re: [GENERAL] How to upgrade PostgreSQL minor version?

2017-04-26 Thread Ron Ben 


Hi :)
I do have a test enviroment and I will test eveything there first but I had cases where eveything worked on test but on production I had difficultes. It's rare but can happen.
Problems with upgrading PostgreSQL isn't like upgrading a package which effects few functions.
Since the documntation says just replace the executables I'm lost...
I notced that 9.3.5 and 9.3.9 requires sepcific test. Further more it says secificly not to perform the tests for 9.3.9 with versions piror to 9.3.5 as it might not work : "The lingering effects of a previously-fixed bug in pg_upgrade could also cause such a failure, in installations that had used pg_upgrade versions between 9.3.0 and 9.3.4."  So this means I have to upgrade to 9.3.5 and only then to 9.3.16
How do I perform upgrade in steps to specific versions?
 
I'm runing 9.3.3 in production server and in test server.
Operating system is Ubuntu server 12
 
I'm still not sure that I can upgrade directly from 9.3.3 to 9.3.16 - The documntation always tells to "if you are upgrading from version earlier than 9.3.X check 9.3.X-1". It assums that everyone upgrading once the version was released which is not always the case. And as mentioned earlier 9.3.5 and 9.3.9 can create problems as they require specific checks.
 
 
I'd appriciate any insight.
 
 
 
 


ב אפר׳ 26, 2017 13:26, Sameer Kumar כתב:



On Wed, Apr 26, 2017 at 4:41 PM Ron Ben  wrote:


I'm runing PostgreSQL 9.3.3 and I want to upgrade it to the latest 9.3 version
The documontation does not specify what needs to be done other than "just install the executables".
This is wierd as for example 9.3.5 release notes request to run a specifc query to  check for pg_multixact files left.
 
I saw this question:
https://serverfault.com/questions/563667/update-9-3-x-minor-verison-of-postgres-on-ubuntu-12-04
I'm not sure how this will handle the 9.3.5 check issue.
 
 


 
What is the version of PostgreSQL that you are running?
 
Though there are some general guidelines/procedure (which is as simple as stop the server, upgrade binaries and start, there will also be some peculiar releases which has a bug-fix. While the bug-fix covers you in future, there might have been incidents where you are already bitten by bug being fixed (worse if you have not yet identified it). To cover such cases, you would need to follow some custom procedure. Again, mostly the custom steps are not for patch application but more of a mitigation against risks of bug or in some cases bug-fix.
 
Are you running with a standby or just stand-alone setup?
 
 


Is it really just runing one command?
What if there are errors and problems? how do i revert back?
Sadly there is not enough information regarding minor verion updates...
 
I'm also runing ubuntu server 12 which will be updated as well later this quarter.


 
Do you have a test environment? If I were you I would have a non-prod that mimics my production setup exactly and I would apply the patch there first.  
 


-- 


-- 













Best Regards,
Sameer Kumar | Senior Solution Architect
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
T: +65 6438 3504 | www.ashnik.com
Skype: sameer.ashnik |   M: +65 8110 0350

[GENERAL] GENERAL - [How to check if the array contains the element.]

2017-04-26 Thread VENKTESH GUTTEDAR 
Hello All,

How do i check if any one element of one array exists in another array.
Eg:
array1 = [1,2,3,4]
array2 = [1,4,5,7]
Now i expect the result as true even if any one element matches from
array2 in array1.

Any help would be appreciated.

Thank you.

-- 
Regards :
Venktesh Guttedar.

Re: [GENERAL] GENERAL - [How to check if the array contains the element.]

2017-04-26 Thread Andreas Kretschmer 



Am 26.04.2017 um 13:24 schrieb VENKTESH GUTTEDAR:

Hello All,

How do i check if any one element of one array exists in another 
array.

Eg:
array1 = [1,2,3,4]
array2 = [1,4,5,7]
Now i expect the result as true even if any one element matches 
from array2 in array1.


You can use the overlap - operator:

test=*# select array[1,2,3,4] && array[1,4,5,7];
 ?column?
--
 t
(1 Zeile)

test=*# select array[1,2,3,4] && array[10,40,50,70];
 ?column?
--
 f
(1 Zeile)


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] GENERAL - [How to check if the array contains the element.]

2017-04-26 Thread Pavel Stehule 
Hi

2017-04-26 13:33 GMT+02:00 Andreas Kretschmer :

>
>
> Am 26.04.2017 um 13:24 schrieb VENKTESH GUTTEDAR:
>
>> Hello All,
>>
>> How do i check if any one element of one array exists in another
>> array.
>> Eg:
>> array1 = [1,2,3,4]
>> array2 = [1,4,5,7]
>> Now i expect the result as true even if any one element matches from
>> array2 in array1.
>>
>
> You can use the overlap - operator:
>
> test=*# select array[1,2,3,4] && array[1,4,5,7];
>  ?column?
> --
>  t
> (1 Zeile)
>
> test=*# select array[1,2,3,4] && array[10,40,50,70];
>  ?column?
> --
>  f
> (1 Zeile)
>
>
or subset

postgres=# select array[1,2,3,4] @> array[1,3,2,4];
┌──┐
│ ?column? │
╞══╡
│ t│
└──┘
(1 row)



> Regards, Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Questions regarding JSON processing

2017-04-26 Thread Glen Huang 
@Pavel

Thanks for bringing PLV8 to my attention. Wasn't aware of it. Sounds like the 
right tool to for the job. I'll try it out. Do you think it makes sense to use 
PLV8 to also generate JSON? Can it beat SQL?

Good to know functions are executed under transaction, I think that should be 
enough for me.

@John

Only data is inside JSON, but it does have keys like "added", "updated" that 
contain objected to be added and updated inside it. I think this kind of 
branching should be safe though?

On Wed, Apr 26, 2017 at 12:41 PM, Pavel Stehule mailto:pavel.steh...@gmail.com>> wrote:


2017-04-26 6:21 GMT+02:00 Glen Huang mailto:hey...@gmail.com>>:
Hi all,

I have a RESTful API server that sends and receives JSON strings. I'm wondering 
what might be the best way to leverage PostgreSQL's JSON capability.

For sending JSON responses to clients. I believe the best way is to ask 
PostgreSQL to generate the JSON string and then pass that directly to clients, 
instead of making multiple queries to construct the JSON and then send it,  
which doesn't seem optimal. Is that the case?

For updating db using JSON requests from clients, that I'm not so sure. Should 
I directly pass the request JSON to PostgreSQL and ask it to parse this JSON 
and execute a transaction all by itself, or should I parse it in the server and 
generate the transaction SQL and execute that on PostgreSQL? The former sounds 
optimal, but I'm not sure if PostgreSQL is able to walk a JSON structure and 
run a transaction along the way? Should I do it with PL/pgSQL? It seems 
functions can't execute a transaction?

The PLpgSQL is static language and is good for static processing JSON doc, but 
it is unfit for iteration over any generic nested document. You can use 
PLPerlu, PLPythonu. Lot of people uses PLV8 for JSON processing.

The functions in PostgreSQL are executed under transaction - you cannot to 
explicitly control transaction, but there are possibility to implicitly handle 
transactions with exception handling. There is workaround via dblink to emulate 
autonomous transactions.

Regards

Pavel
 

Would like to hear some thoughts on this. Thanks.

Glen

Re: [GENERAL] Questions regarding JSON processing

2017-04-26 Thread Pavel Stehule 
2017-04-26 15:06 GMT+02:00 Glen Huang :

> @Pavel
>
> Thanks for bringing PLV8 to my attention. Wasn't aware of it. Sounds like
> the right tool to for the job. I'll try it out. Do you think it makes sense
> to use PLV8 to also generate JSON? Can it beat SQL?
>

Hard to say - probably it depends on actual case. I have not any
benchmarks.

Regards

Pavel


>
> Good to know functions are executed under transaction, I think that should
> be enough for me.
>
> @John
>
> Only data is inside JSON, but it does have keys like "added", "updated"
> that contain objected to be added and updated inside it. I think this kind
> of branching should be safe though?
>
> On Wed, Apr 26, 2017 at 12:41 PM, Pavel Stehule 
> wrote:
>
>>
>>
>> 2017-04-26 6:21 GMT+02:00 Glen Huang :
>>
>>> Hi all,
>>>
>>> I have a RESTful API server that sends and receives JSON strings. I'm
>>> wondering what might be the best way to leverage PostgreSQL's JSON
>>> capability.
>>>
>>> For sending JSON responses to clients. I believe the best way is to ask
>>> PostgreSQL to generate the JSON string and then pass that directly to
>>> clients, instead of making multiple queries to construct the JSON and then
>>> send it,  which doesn't seem optimal. Is that the case?
>>>
>>> For updating db using JSON requests from clients, that I'm not so sure.
>>> Should I directly pass the request JSON to PostgreSQL and ask it to parse
>>> this JSON and execute a transaction all by itself, or should I parse it in
>>> the server and generate the transaction SQL and execute that on PostgreSQL?
>>> The former sounds optimal, but I'm not sure if PostgreSQL is able to walk a
>>> JSON structure and run a transaction along the way? Should I do it with
>>> PL/pgSQL? It seems functions can't execute a transaction?
>>>
>>
>> The PLpgSQL is static language and is good for static processing JSON
>> doc, but it is unfit for iteration over any generic nested document. You
>> can use PLPerlu, PLPythonu. Lot of people uses PLV8 for JSON processing.
>>
>> The functions in PostgreSQL are executed under transaction - you cannot
>> to explicitly control transaction, but there are possibility to implicitly
>> handle transactions with exception handling. There is workaround via dblink
>> to emulate autonomous transactions.
>>
>> Regards
>>
>> Pavel
>>
>>
>>>
>>> Would like to hear some thoughts on this. Thanks.
>>>
>>> Glen
>>>
>>
>>
>

Re: [GENERAL] How to upgrade PostgreSQL minor version?

2017-04-26 Thread Adrian Klaver 

On 04/26/2017 03:36 AM, Ron Ben wrote:

Hi :)

I do have a test enviroment and I will test eveything there first but I
had cases where eveything worked on test but on production I had
difficultes. It's rare but can happen.
Problems with upgrading PostgreSQL isn't like upgrading a package which
effects few functions.
Since the documntation says just replace the executables I'm lost...
I notced that 9.3.5 and 9.3.9 requires sepcific test. Further more it
says secificly not to perform the tests for 9.3.9 with versions piror to
9.3.5 as it might not work : "The lingering effects of a
previously-fixed bug in pg_upgrade could also cause such a failure, in
installations that had used pg_upgrade versions between 9.3.0 and
9.3.4."  So this means I have to upgrade to 9.3.5 and only then to 9.3.16


This only applies if you used the pg_upgrade program to upgrade to a 9.3 
cluster. If you used dump/restore it does not apply:


https://www.postgresql.org/docs/9.3/static/release-9-3-5.html
"
In pg_upgrade, remove pg_multixact files left behind by initdb (Bruce 
Momjian)


If you used a pre-9.3.5 version of pg_upgrade to upgrade a database 
cluster to 9.3, it might have left behind a file 
$PGDATA/pg_multixact/offsets/ that should not be there and will 
eventually cause problems in VACUUM. However, in common cases this file 
is actually valid and must not be removed. To determine whether your 
installation has this problem, run this query as superuser, in any 
database of the cluster:


WITH list(file) AS (SELECT * FROM pg_ls_dir('pg_multixact/offsets'))
SELECT EXISTS (SELECT * FROM list WHERE file = '') AND
   NOT EXISTS (SELECT * FROM list WHERE file = '0001') AND
   NOT EXISTS (SELECT * FROM list WHERE file = '') AND
   EXISTS (SELECT * FROM list WHERE file != '')
   AS file__removal_required;

If this query returns t, manually remove the file 
$PGDATA/pg_multixact/offsets/. Do nothing if the query returns f.

"


How do I perform upgrade in steps to specific versions?


9.3.3 -- > 9.3.4

https://www.postgresql.org/docs/9.3/static/release-9-3-4.html

"However, the error fixed in the first changelog entry below could have 
resulted in corrupt data on standby servers. It may be prudent to 
reinitialize standby servers from fresh base backups after installing 
this update."


So if you are not running any standbys a no-op.

9.3.4 --> 9.3.5

The above mentioned pg_upgrade bug, so if you did not use pg_upgrade 
another no-op.


9.3.5 -- > 9.3.6

https://www.postgresql.org/docs/9.3/static/release-9-3-6.html

"However, if you are a Windows user and are using the "Norwegian 
(Bokmål)" locale, manual action is needed after the upgrade to replace 
any "Norwegian (Bokmål)_Norway" locale names stored in PostgreSQL system 
catalogs with the plain-ASCII alias "Norwegian_Norway". For details see 
http://wiki.postgresql.org/wiki/Changes_To_Norwegian_Locale";


So if you are not using the Norwegian local on Windows another no-op.

9.3.6 -- > 9.3.7

https://www.postgresql.org/docs/9.3/static/release-9-3-7.html

"However, if you use contrib/citext's regexp_matches() functions, see 
the changelog entry below about that."


9.3.7 -- > 9.3.8

https://www.postgresql.org/docs/9.3/static/release-9-3-8.html

If you have covered the above nothing to do.

9.3.8 -- > 9.3.9

https://www.postgresql.org/docs/9.3/static/release-9-3-9.html

"However, if you are upgrading an installation that was previously 
upgraded using a pg_upgrade version between 9.3.0 and 9.3.4 inclusive, 
see the first changelog entry below."


If you did not use pg_upgrade a no-op.

9.3.9 --> 9.3.14

All refer you back to the above entry for 9.3.9

9.3.14 --> 9.3.15

https://www.postgresql.org/docs/9.3/static/release-9-3-15.html

"However, if your installation has been affected by the bug described in 
the first changelog entry below, then after updating you may need to 
take action to repair corrupted free space maps.




Fix WAL-logging of truncation of relation free space maps and visibility 
maps (Pavan Deolasee, Heikki Linnakangas)


It was possible for these files to not be correctly restored during 
crash recovery, or to be written incorrectly on a standby server. Bogus 
entries in a free space map could lead to attempts to access pages that 
have been truncated away from the relation itself, typically producing 
errors like "could not read block XXX: read only 0 of 8192 bytes". 
Checksum failures in the visibility map are also possible, if 
checksumming is enabled.


Procedures for determining whether there is a problem and repairing it 
if so are discussed at 
https://wiki.postgresql.org/wiki/Free_Space_Map_Problems.

"

9.3.15 --> 9.3.16

https://www.postgresql.org/docs/9.3/static/release-9-3-16.html

"However, if your installation has been affected by the bug described in 
the first changelog entry below, then after updating you may need to 
take action to repair corrupted indexes.


...

Fix a race condition that could cause

Re: [GENERAL] How to upgrade PostgreSQL minor version?

2017-04-26 Thread Ron Ben

Re: [GENERAL] How to upgrade PostgreSQL minor version?

2017-04-26 Thread David G. Johnston 
​The convention on these lists is to bottom-post.

On Wed, Apr 26, 2017 at 6:56 AM, Ron Ben  wrote:

> Hi,
>
> What are standbys?
>

High-level: ​a standby is a second server, running the same version of
PostgreSQL, the reads the real-time backup/WAL data from the primary server
and restores it to its own files; so that at any given moment it is in sync
with the primary and could take over for the primary should it encounter
machine failure (or otherwise go offline).​

My question was more of how to actualy do the update to a specific versions.
> What are the commands to do it? normaly the default upgrade is for the
> latest version.
> I'm not sure what is the recommndation. Should I just update directly to
> 9.3.16? It's very confusing how to do it somoothly.
>

Personally, I use Ubuntu and running 
​works just fine for me...

The reason the docs don't cover this is because the docs assume
installation from source.  You need to inquire with whomever provided your
packages as to how they work.

Typically one would just upgrade to the new patch release in a major
version.  While certainly possible I don't know of any situations where not
doing that would be beneficial.

As for the release notes - they cannot cover every possible combination of
upgrade paths.  You'd need to read them and figure out how to adapt to your
situation.  They assume that once you are on a major release you are
keeping up with the bugfix releases.

If you encounter a failure while/after running a minor update you either
need to restore the executables from backup or see if your packaging system
can help.  I guess the answer would really depend on how things failed -
and given that such failures are relatively rare little effort has gone on
to make reversion within minor releases easier.

There is something to be said for standing up a new VM running the new
binaries and falling back to the old VM running the old binaries if there
is a problem.  The data files want to be kept separate and mounted to the
running VM with the desired version.

David J.

Re: [GENERAL] How to upgrade PostgreSQL minor version?

2017-04-26 Thread Adrian Klaver 

On 04/26/2017 06:56 AM, Ron Ben wrote:

Hi,

What are standbys?


Standbys are the standby servers when you are using replication:

https://www.postgresql.org/docs/9.3/static/high-availability.html

I am guessing you are not using replication.


My question was more of how to actualy do the update to a specific versions.


That depends on how you installed Postgres. If you used packages then 
you can use the package manager. If you built from source, then you need 
to download the latest and build it but not install it yet. Shutdown the 
existing running instance of Postgres and then install the latest 
version. In the current Postgres version numbering system X.X.x the X 
refers to major versions the x to a minor version. As a rule minor 
version to minor version upgrades do not require a dump/restore cycle or 
use of pg_upgrade to deal with the date. You are just upgrading the 
binaries. Since it is a rule there have been exceptions so is important 
to read the release notes. FYI, starting with Postgres 10 the versioning 
will change to X.x. Since this is your first time going through the 
process I would dumpall the Postgres cluster first, before you do the 
upgrade, just to be on the safe side:


https://www.postgresql.org/docs/9.3/static/app-pg-dumpall.html




What are the commands to do it? normaly the default upgrade is for the
latest version.


See above.


I'm not sure what is the recommndation. Should I just update directly to
9.3.16? It's very confusing how to do it somoothly.


The changes in the various versions from 9.3.4 to 9.3.16 are cumulative 
so by going directly to 9.3.16 you get all of them. You just need to go 
through the list I provided to see if any of them actually apply to you 
and deal with them in the 9.3.16 instance you start up.







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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] The Contractor Conundrum

2017-04-26 Thread Melvin Davidson 
This article is written as my experience as a PostgreSQL contractor and to
all those that that think
they might want to be one.

It all began about 4 years ago when, oddly enough, I retired. Shortly after
I moved to my new,
and final home ( I hope), I began recieving calls from headhunters wanting
to place me in companies
that required me to relocate. Having had to relocate 4 times in 8 years due
to a bad job market,
I was in no mood to due it again, so I politely informed them I was not
interested and hung up
the phone. That  was until I recieved a call that advised me I only had to
be on site for two
days and then I could work remotely from home. I was asked what my hourly
rate would be and I
just threw something out there, not expecting them to be ok with it, but
they were. So I took
the job (and trip) and was impressed with the company and personnel I was
working with. The
database was well designed and I was given a laptop to connect from my home
with. I was given
the task of testing new auxillary software for PostgreSQL and writing bash
scripts to insure
backups and restores were being done and were reliable. That position
lasted 6 months and I left
with a feeling of having completed a job well done. However, my next
contract was a nightmare.
The database was poorly designed. Instead of a simple scaled database, each
customer was given
their own schema, with a copy of each table in it. What was really bad was
they had put an index
on every column of every table, only a few of which were actually used in
queries. I begged them
to let me drop the unused indexes so they could save space and optimize
inserts, updates & deletes, but they refused because "they had higher
priorities".
Now, my latest commitment is somewhat better, but not without it's own
annoyances. Primarily,
despite my repeated attempts to convince them otherwise, the developers at
that site seem to feel
it is essential to constantly issue queries of "SELECT 1;", so that they do
not lose the connection
to the database. That of course, has resulted at times in no connection
being available for other
users/developers. Let alone the fact that developers should not be allowed
on production systems.
I have advised them they are much better off using a connection manager
like PgBouncer, but they apparently do not understand the concept or
benefit. In summary, I can only advise that aspiring contractors find out
as much about a system/schema/policies before commiting to a contract, no
matter how much they offer to pay.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] The Contractor Conundrum

2017-04-26 Thread Geoff Winkless 
On 26 April 2017 at 15:45, Melvin Davidson  wrote:

> In summary, I can only advise that aspiring contractors find out as much
> about a system/schema/policies before commiting to a contract, no matter
> how much they offer to pay.


An interesting perspective, thanks.

>From the other side, I'd be more inclined to say, be prepared to do what is
asked of you and no more, no matter how much the temptation would be to say
"but if you just...".

Contractors are treated with suspicion by perm staff, partly because of
financial jealousy (most salaried staff can't even dream of the sort of
rates contractors will command) but also because outsiders will often point
out previous poor work to the boss, often with no understanding of the
historical reasons behind it.

Trying to change people's attitudes, unless you're explicitly brought in
with that brief, is likely to provoke exactly that sort of negative
pushback, and does little but make your life harder.

​Geoff​

Re: [GENERAL] The Contractor Conundrum

2017-04-26 Thread Andrew Kerber 
All I can say is welcome to the world of contracting. You have successfully
described the nature of the business.

On Wed, Apr 26, 2017 at 10:33 AM, Geoff Winkless 
wrote:

> On 26 April 2017 at 15:45, Melvin Davidson  wrote:
>
>> In summary, I can only advise that aspiring contractors find out as much
>> about a system/schema/policies before commiting to a contract, no matter
>> how much they offer to pay.
>
>
> An interesting perspective, thanks.
>
> From the other side, I'd be more inclined to say, be prepared to do what
> is asked of you and no more, no matter how much the temptation would be to
> say "but if you just...".
>
> Contractors are treated with suspicion by perm staff, partly because of
> financial jealousy (most salaried staff can't even dream of the sort of
> rates contractors will command) but also because outsiders will often point
> out previous poor work to the boss, often with no understanding of the
> historical reasons behind it.
>
> Trying to change people's attitudes, unless you're explicitly brought in
> with that brief, is likely to provoke exactly that sort of negative
> pushback, and does little but make your life harder.
>
> ​Geoff​
>
>


-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: [GENERAL] TimeScaleDB -- Open Source Time Series Database Released (www.i-programmer.info);

2017-04-26 Thread Joshua D. Drake 

On 04/10/2017 05:22 AM, Steve Petrie, P.Eng. wrote:

Please see below.


TimeseriesDB is an interesting project. They actually sponsored at spoke 
at PGConf US. You can see their presentation here:


https://www.pgconf.us/conferences/2017/program/proposals/372

Thanks,

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] LIMIT clause slowing down query in some cases, accelerating in others

2017-04-26 Thread Klaus P. Pieper 
Running PostgreSQL 9.6 on a Windows Server. 

Table "t" is kind of a materialized view with > 100 columns and 2.24 Mio
rows. Queries are generated by an ORM framework - fairly difficult to
modify. 

Vacuum analyze was carried out - no impact. 

 

The framework generates queries like this: 

 

select N0."uorderid" from "t" N0

where (N0."szzip" like E'33%')

order by N0."szzip" asc nulls last LIMIT 128 OFFSET 0

 

EXPLAIN ANALYZE: 

Limit  (cost=0.43..547.08 rows=128 width=21) (actual time=402.247..402.386
rows=128 loops=1)

  ->  Index Only Scan using t_szzip_uorderid_idx1 on t n0
(cost=0.43..83880.65 rows=19641 width=21) (actual time=402.244..402.344
rows=128 loops=1)

Filter: ((szzip)::text ~~ '33%'::text)

Rows Removed by Filter: 699108

Heap Fetches: 0

Planning time: 0.687 ms

Execution time: 402.443 ms

 

EXPLAIN ANALYZE without LIMIT and OFFSET:

Sort  (cost=66503.14..66552.24 rows=19641 width=21) (actual
time=151.598..156.155 rows=24189 loops=1)

  Sort Key: szzip

  Sort Method: quicksort  Memory: 2658kB

  ->  Bitmap Heap Scan on t n0  (cost=200.22..65102.58 rows=19641 width=21)
(actual time=21.267..90.272 rows=24189 loops=1)

Recheck Cond: ((szzip)::text ~~ '33%'::text)

Rows Removed by Index Recheck: 26

Heap Blocks: exact=23224

->  Bitmap Index Scan on t_szzip_idx_gin  (cost=0.00..195.31
rows=19641 width=0) (actual time=14.235..14.235 rows=24215 loops=1)

  Index Cond: ((szzip)::text ~~ '33%'::text)

Planning time: 0.669 ms

Execution time: 161.860 ms

 

With LIMIT, a btree index is used whereas without the LIMIT clause, a GIN
index is used. 

 

Unfortunately, modifying the LIKE query parameter from E'33%' to E'10%'
gives completely different results:

select N0."uorderid" from "t" N0

where (N0."szzip" like E'10%')

order by N0."szzip" asc nulls last LIMIT 128 OFFSET 0

 

EXPLAIN ANALYZE: 

Limit  (cost=0.43..195.08 rows=128 width=21) (actual time=88.699..88.839
rows=128 loops=1)

  ->  Index Only Scan using t_szzip_uorderid_idx1 on t n0
(cost=0.43..83880.65 rows=55158 width=21) (actual time=88.696..88.793
rows=128 loops=1)

Filter: ((szzip)::text ~~ '10%'::text)

Rows Removed by Filter: 142107

Heap Fetches: 0

Planning time: 0.669 ms

Execution time: 88.900 ms

 

EXPLAIN ANALYZE without LIMIT and OFFSET:

Index Only Scan using t_szzip_uorderid_idx1 on t n0  (cost=0.43..83880.65
rows=55158 width=21) (actual time=88.483..1263.396 rows=53872 loops=1)

  Filter: ((szzip)::text ~~ '10%'::text)

  Rows Removed by Filter: 2192769

  Heap Fetches: 0

Planning time: 0.671 ms

Execution time: 1274.761 ms

 

In this case, the GIN index is not used at all. 

 

Anything else I can do about this?