Re: Inconsistent compilation error

[Adrian Klaver]

On 04/18/2018 06:02 PM, r...@raf.org wrote:

Hi,





But I'm getting this compilation error when it tries to load this
function:

 ERROR:  too many parameters specified for RAISE
 CONTEXT:  compilation of PL/pgSQL function "tla_audit_delete_thing" near 
line 9

 Traceback (most recent call last):
   File "lib/loadfunc.py", line 228, in main
 db.cursor().execute(src)
   File "/usr/lib/python2.7/dist-packages/pgdb.py", line 1026, in execute
 return self.executemany(operation, [parameters])
   File "/usr/lib/python2.7/dist-packages/pgdb.py", line 1050, in 
executemany
 rows = self._src.execute(sql)
 ProgrammingError: ERROR:  too many parameters specified for RAISE
 CONTEXT:  compilation of PL/pgSQL function "tla_audit_delete_thing" near 
line 9

The line in question is:

 raise notice '% %', 'blah_history.original_id', r;

Which looks fine. The really wierd thing is that this happens when done on a
debian9 host but when I load the function from another host (my macos laptop)
with the same function into the same database, it works fine.

I've never encountered an inconsistency like this before.

Any suggestions as to what might be causing it?

The python versions are slightly different and the pgdb module versions
are different but I wouldn't have thought that that would affect the
compilation performed by the database server itself:

   debian9:   python-2.7.13 pgdb-5.0.3


Hmm, wonder if there is an oops in the below:

http://www.pygresql.org/contents/changelog.html

Version 5.0 (2016-03-20)
Changes in the DB-API 2 module (pgdb):
"SQL commands are always handled as if they include parameters, i.e. 
literal percent signs must always be doubled. This consistent behavior 
is necessary for using pgdb with wrappers like SQLAlchemy."




   macos-10.11.6: python-2.7.14 pgdb-4.2.2

And the sql sent to the database server is identical from both hosts.

And I don't think anything much has changed on the debian host recently.

And it's not just the old backup. The same is happening with other copies of
essentially the same database.

And all the other stored functions were loaded fine. It's just this one that
went wrong.

Thanks in advance for any insights you can share.

cheers,
raf






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

Re: Inconsistent compilation error

[Adrian Klaver]

On 04/18/2018 06:02 PM, r...@raf.org wrote:

Hi,

postgresql-9.5.12 on debian-9

I have a stored function with code that looks like:

 create or replace function tla_audit_delete_thing()
 returns boolean stable language plpgsql as $$
 declare
 r record;
 status boolean := 1;
 begin
 for r in select _.* from blah_history _ where _.original_id not in 
(select id from blah)
 loop
 raise notice '% %', 'blah_history.original_id', r;
 status := 0;
 end loop;
 [...]
 end
 $$
 security definer
 set search_path = public, pg_temp;
 revoke all on function tla_audit_delete_thing() from public;
 grant execute on function tla_audit_delete_thing() to staff;


So if you cut and paste the above directly into the database via psql on 
the Debian host do you see the same issue?




And I have a program that loads stored functions from disk
when they are different to what's in the database and I have
just loaded a very old database backup, brought the schema up
to date, and tried to bring the stored functions up to date.





cheers,
raf






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

Re: Inconsistent compilation error

[David G. Johnston]

On Wed, Apr 18, 2018 at 7:04 PM, Melvin Davidson 
wrote:

>
> Normally, literals are inside the first quotes. IE: raise notice '
> blah_history.original_id' %', r;
>

​But a compiler doesn't care about human concepts like "normally" - it just
cares about "syntactically correct" and as David and Tom observe the
original post as visually observed is correct.  Which leads one to think
that reality and the original post somehow don't match, or as Tom said what
the server sees and what is sent end up being different (client encoding
issues or some such).

And pretty sure "r" being NULL just puts an empty string where the % is.

David J.

Re: Inconsistent compilation error

[David Rowley]

On 19 April 2018 at 13:28, Melvin Davidson  wrote:
> It is not fine. You have specifed TWO percent signs (%) which requires TWO
> argumenrts,
> but you have only provided ONE -> r.

I'm confused about this statement.

Did you perhaps overlook the fact that there are in fact two
arguments? I'm unsure why you're considering
'blah_history.original_id' not to be an argument here.


-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Inconsistent compilation error

[Melvin Davidson]

On Wed, Apr 18, 2018 at 9:02 PM,  wrote:

> Hi,
>
> postgresql-9.5.12 on debian-9
>
> I have a stored function with code that looks like:
>
> create or replace function tla_audit_delete_thing()
> returns boolean stable language plpgsql as $$
> declare
> r record;
> status boolean := 1;
> begin
> for r in select _.* from blah_history _ where _.original_id not in
> (select id from blah)
> loop
> raise notice '% %', 'blah_history.original_id', r;
> status := 0;
> end loop;
> [...]
> end
> $$
> security definer
> set search_path = public, pg_temp;
> revoke all on function tla_audit_delete_thing() from public;
> grant execute on function tla_audit_delete_thing() to staff;
>
> And I have a program that loads stored functions from disk
> when they are different to what's in the database and I have
> just loaded a very old database backup, brought the schema up
> to date, and tried to bring the stored functions up to date.
>
> But I'm getting this compilation error when it tries to load this
> function:
>
> ERROR:  too many parameters specified for RAISE
> CONTEXT:  compilation of PL/pgSQL function "tla_audit_delete_thing"
> near line 9
>
> Traceback (most recent call last):
>   File "lib/loadfunc.py", line 228, in main
> db.cursor().execute(src)
>   File "/usr/lib/python2.7/dist-packages/pgdb.py", line 1026, in
> execute
> return self.executemany(operation, [parameters])
>   File "/usr/lib/python2.7/dist-packages/pgdb.py", line 1050, in
> executemany
> rows = self._src.execute(sql)
> ProgrammingError: ERROR:  too many parameters specified for RAISE
> CONTEXT:  compilation of PL/pgSQL function "tla_audit_delete_thing"
> near line 9
>
> The line in question is:
>
> raise notice '% %', 'blah_history.original_id', r;
>
> Which looks fine. The really wierd thing is that this happens when done on
> a
> debian9 host but when I load the function from another host (my macos
> laptop)
> with the same function into the same database, it works fine.
>
> I've never encountered an inconsistency like this before.
>
> Any suggestions as to what might be causing it?
>
> The python versions are slightly different and the pgdb module versions
> are different but I wouldn't have thought that that would affect the
> compilation performed by the database server itself:
>
>   debian9:   python-2.7.13 pgdb-5.0.3
>   macos-10.11.6: python-2.7.14 pgdb-4.2.2
>
> And the sql sent to the database server is identical from both hosts.
>
> And I don't think anything much has changed on the debian host recently.
>
> And it's not just the old backup. The same is happening with other copies
> of
> essentially the same database.
>
> And all the other stored functions were loaded fine. It's just this one
> that
> went wrong.
>
> Thanks in advance for any insights you can share.
>
> cheers,
> raf
>
>
>









*>The line in question is:>>raise notice '% %',
'blah_history.original_id', r;>>Which looks fine. It is not fine. You have
specifed TWO percent signs (%) which requires TWO argumenrts,but you have
only provided ONE -> r.*

*Hence->  ERROR:  too many parameters specified for RAISE *





*https://www.postgresql.org/docs/9.6/static/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE
"
Inside the format string, % is replaced by the string representation of the
next optional argument's value"*--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Inconsistent compilation error

[raf]

Hi,

postgresql-9.5.12 on debian-9

I have a stored function with code that looks like:

create or replace function tla_audit_delete_thing()
returns boolean stable language plpgsql as $$
declare
r record;
status boolean := 1;
begin
for r in select _.* from blah_history _ where _.original_id not in 
(select id from blah)
loop
raise notice '% %', 'blah_history.original_id', r;
status := 0;
end loop;
[...]
end
$$
security definer
set search_path = public, pg_temp;
revoke all on function tla_audit_delete_thing() from public;
grant execute on function tla_audit_delete_thing() to staff;

And I have a program that loads stored functions from disk
when they are different to what's in the database and I have
just loaded a very old database backup, brought the schema up
to date, and tried to bring the stored functions up to date.

But I'm getting this compilation error when it tries to load this
function:

ERROR:  too many parameters specified for RAISE
CONTEXT:  compilation of PL/pgSQL function "tla_audit_delete_thing" near 
line 9

Traceback (most recent call last):
  File "lib/loadfunc.py", line 228, in main
db.cursor().execute(src)
  File "/usr/lib/python2.7/dist-packages/pgdb.py", line 1026, in execute
return self.executemany(operation, [parameters])
  File "/usr/lib/python2.7/dist-packages/pgdb.py", line 1050, in executemany
rows = self._src.execute(sql)
ProgrammingError: ERROR:  too many parameters specified for RAISE
CONTEXT:  compilation of PL/pgSQL function "tla_audit_delete_thing" near 
line 9

The line in question is:

raise notice '% %', 'blah_history.original_id', r;

Which looks fine. The really wierd thing is that this happens when done on a
debian9 host but when I load the function from another host (my macos laptop)
with the same function into the same database, it works fine.

I've never encountered an inconsistency like this before.

Any suggestions as to what might be causing it?

The python versions are slightly different and the pgdb module versions
are different but I wouldn't have thought that that would affect the
compilation performed by the database server itself:

  debian9:   python-2.7.13 pgdb-5.0.3
  macos-10.11.6: python-2.7.14 pgdb-4.2.2

And the sql sent to the database server is identical from both hosts.

And I don't think anything much has changed on the debian host recently.

And it's not just the old backup. The same is happening with other copies of
essentially the same database.

And all the other stored functions were loaded fine. It's just this one that
went wrong.

Thanks in advance for any insights you can share.

cheers,
raf

Re: New website

[Adrian Klaver]

On 04/18/2018 05:31 PM, Peter Geoghegan wrote:

On Wed, Apr 18, 2018 at 3:40 PM, Adrian Klaver
 wrote:

And to get to the mailing list archives(arguably one of the most important
links) you have to:

Click on Community, click on Mailing Lists on the left sidebar, then scroll
to the bottom of page to find the search box.


I look at the mailing list archives as much as anyone else does, and I
don't think that it's very important for it to have prominent
placement. Even still, the only difference here is the scrolling. It
has fundamentally the same structure as before.


I am one of the No votes in the survey.


This sounds pretty far from constructive to me, which automatically
detracts from what you're saying.



The same could be said of a survey that only has single Yes/No question 
and no provision for drilling down to what works or does not work for 
you. At any rate see my reply to Jonathan for my initial critique.


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

Re: New website

[Adrian Klaver]

On 04/18/2018 05:22 PM, Jonathan S. Katz wrote:



On Apr 18, 2018, at 6:40 PM, Adrian Klaver  wrote:


On 04/18/2018 03:07 PM, Peter Geoghegan wrote:
On Wed, Apr 18, 2018 at 3:03 PM, Adrian Klaver
 wrote:

I would contact the Webmaster but Contact goes to a big image of an elephant
head. That is also where Downloads, Support and Donate lands. Might have
been a good idea to roll out a demo site for testing first. Will reserve
judgment on the site design until it is functioning.

I really don't think it's practical to give everyone a veto on a
website design. It took years to launch this website redesign.
Honestly, I was beginning to think that it would never happen.
Anyway, I did notice something myself, which is that the git logo
links to https://git.postgresql.org/gitweb/, which has many
non-very-important git repos. Instead, it should point to the main
PostgreSQL repository's gitweb page, which is at
https://git.postgresql.org/gitweb/?p=postgresql.git.


And to get to the mailing list archives(arguably one of the most important 
links) you have to:


We designed the website with 3 user profiles:

- New to PostgreSQL > - Users
- Hackers >
These are 3 very different experiences. It’s hard
to find a balance for all of these different needs.

When we analyzed the traffic we opted to make
various trade offs based on what the most
accessed pages.

We did note that the people who need to access
the mailing list archives know how to get there.


Under the old system. In the new system it took me a bunch of clicking 
around to find the new location.




The nice thing about a website is that it is easy
to iterate over and we can rapidly add things in
should they be necessary.


Click on Community, click on Mailing Lists on the left sidebar, then scroll to 
the bottom of page to find the search box.

I am one of the No votes in the survey.


I’m sorry to hear that.  However based on our data
we opted to focus optimizing the experience for
getting people using PostgreSQL in their apps to
the right places.  If you have constructive feedback
on how we can improve in other areas I’m happy to
listen.  As someone who does answer webmas...@postgresql.org feel free
to reach out.


To start:

Home Page

Picture(its a nice picture) takes up a third page of the page to display 
two buttons. One Download replicates the tab just above. The second 'New 
to Postgrsql? replicates the section just below it.


New to PostgreSQL?  should be in the tabs above. Not sure why it is more 
important then the News buried down at the bottom:

"
PostgreSQL 10.3 Released!

This release centers around added documentation that describes 
CVE-2018-1058 and how to take steps to mitigate the impact on PostgreSQL 
databases. There are also several bug fixes included in the release. All 
users using the affected versions of PostgreSQL should update as soon as 
possible.

"

Clicking on the Mailing List link on front page should take you to page 
that includes the archives.


The acres of white space needs to be eliminated to bring information 
closer together and eliminate wear and tear on the scroll wheel.




Jonathan




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

Re: New website

[Peter Geoghegan]

On Wed, Apr 18, 2018 at 3:40 PM, Adrian Klaver
 wrote:
> And to get to the mailing list archives(arguably one of the most important
> links) you have to:
>
> Click on Community, click on Mailing Lists on the left sidebar, then scroll
> to the bottom of page to find the search box.

I look at the mailing list archives as much as anyone else does, and I
don't think that it's very important for it to have prominent
placement. Even still, the only difference here is the scrolling. It
has fundamentally the same structure as before.

> I am one of the No votes in the survey.

This sounds pretty far from constructive to me, which automatically
detracts from what you're saying.

-- 
Peter Geoghegan

Re: New website

[Jonathan S. Katz]

> On Apr 18, 2018, at 6:40 PM, Adrian Klaver  wrote:
> 
>> On 04/18/2018 03:07 PM, Peter Geoghegan wrote:
>> On Wed, Apr 18, 2018 at 3:03 PM, Adrian Klaver
>>  wrote:
>>> I would contact the Webmaster but Contact goes to a big image of an elephant
>>> head. That is also where Downloads, Support and Donate lands. Might have
>>> been a good idea to roll out a demo site for testing first. Will reserve
>>> judgment on the site design until it is functioning.
>> I really don't think it's practical to give everyone a veto on a
>> website design. It took years to launch this website redesign.
>> Honestly, I was beginning to think that it would never happen.
>> Anyway, I did notice something myself, which is that the git logo
>> links to https://git.postgresql.org/gitweb/, which has many
>> non-very-important git repos. Instead, it should point to the main
>> PostgreSQL repository's gitweb page, which is at
>> https://git.postgresql.org/gitweb/?p=postgresql.git.
> 
> And to get to the mailing list archives(arguably one of the most important 
> links) you have to:

We designed the website with 3 user profiles:

- New to PostgreSQL
- Users
- Hackers

These are 3 very different experiences. It’s hard
to find a balance for all of these different needs.

When we analyzed the traffic we opted to make
various trade offs based on what the most
accessed pages.

We did note that the people who need to access
the mailing list archives know how to get there.

The nice thing about a website is that it is easy
to iterate over and we can rapidly add things in
should they be necessary.

> Click on Community, click on Mailing Lists on the left sidebar, then scroll 
> to the bottom of page to find the search box.
> 
> I am one of the No votes in the survey.

I’m sorry to hear that.  However based on our data
we opted to focus optimizing the experience for
getting people using PostgreSQL in their apps to
the right places.  If you have constructive feedback
on how we can improve in other areas I’m happy to
listen.  As someone who does answer webmas...@postgresql.org feel free
to reach out.

Jonathan

Re: pg_upgrade help

[Rajni Baliyan]

Hello Akshay,

Try starting both servers individually. If you can then,it may be port
conflict

Below is the part of document-

Obviously, no one should be accessing the clusters during the upgrade.
pg_upgrade defaults to running servers on port 50432 to avoid unintended
client connections. You can use the same port number for both clusters when
doing an upgrade because the old and new clusters will not be running at
the same time. *However, when checking an old running server, the old and
new port numbers must be different. *

Thanks
Rajni

On Thu, Apr 19, 2018 at 12:02 AM, Adrian Klaver 
wrote:

> On 04/18/2018 05:02 AM, Akshay Ballarpure wrote:
>
>> Hi Fabio,
>> sorry to bother you again, its still failing with stopping both server
>> (8.4 and 9.4)
>>
>
> Actually according to the command show at bottom of post it is failing
> trying to start the 8.4 server. In your previous post that was because it
> was already running:
>
> -bash-4.2$ ps -eaf | grep postgres
> postgres  9778 1  0 09:17 ?00:00:00 /usr/bin/postgres -p 50432
> -D /var/ericsson/esm-data/postgresql-data/
>
> FATAL:  lock file "postmaster.pid" already exists
> HINT:  Is another postmaster (PID 9778) running in data directory
> "/var/ericsson/esm-data/postgresql-data"?
> pg_ctl: could not start server
>
>
> Make sure both servers are stopped before running pg_upgrade. Per a
> previous suggestion follow the check list here:
>
> https://www.postgresql.org/docs/10/static/pgupgrade.html
> "
> Usage
>
> These are the steps to perform an upgrade with pg_upgrade:
>
> ...
>
>
> "
>
>
>> -bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade
>> --old-bindir=/usr/bin --new-bindir=/opt/rh/rh-postgresql94/root/usr/bin
>> --old-datadir=$OLDCLUSTER --new-datadir=$NEWCLUSTER
>>
>> connection to database failed: could not connect to server: No such file
>> or directory
>>  Is the server running locally and accepting
>>  connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.
>> 50432"?
>>
>>
>> could not connect to old postmaster started with the command:
>> "/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
>> "/var/ericsson/esm-data/postgresql-data" -o "-p 50432 -c autovacuum=off
>> -c autovacuum_freeze_max_age=20  -c listen_addresses='' -c
>> unix_socket_permissions=0700" start
>> Failure, exiting
>>
>>
>> With Best Regards
>> Akshay
>>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>


-- 
Thank you

Sincere Regards
Rajni

0410 472 086

Re: New website

[Adrian Klaver]

On 04/18/2018 03:07 PM, Peter Geoghegan wrote:

On Wed, Apr 18, 2018 at 3:03 PM, Adrian Klaver
 wrote:

I would contact the Webmaster but Contact goes to a big image of an elephant
head. That is also where Downloads, Support and Donate lands. Might have
been a good idea to roll out a demo site for testing first. Will reserve
judgment on the site design until it is functioning.


I really don't think it's practical to give everyone a veto on a
website design. It took years to launch this website redesign.
Honestly, I was beginning to think that it would never happen.

Anyway, I did notice something myself, which is that the git logo
links to https://git.postgresql.org/gitweb/, which has many
non-very-important git repos. Instead, it should point to the main
PostgreSQL repository's gitweb page, which is at
https://git.postgresql.org/gitweb/?p=postgresql.git.



And to get to the mailing list archives(arguably one of the most 
important links) you have to:


Click on Community, click on Mailing Lists on the left sidebar, then 
scroll to the bottom of page to find the search box.


I am one of the No votes in the survey.

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

Re: New website

[Adrian Klaver]

On 04/18/2018 03:07 PM, Peter Geoghegan wrote:

On Wed, Apr 18, 2018 at 3:03 PM, Adrian Klaver
 wrote:

I would contact the Webmaster but Contact goes to a big image of an elephant
head. That is also where Downloads, Support and Donate lands. Might have
been a good idea to roll out a demo site for testing first. Will reserve
judgment on the site design until it is functioning.


I really don't think it's practical to give everyone a veto on a
website design. It took years to launch this website redesign.
Honestly, I was beginning to think that it would never happen.


See here:

https://www.postgresql.org/community/survey/94-do-you-like-the-redesign-of-the-postgresqlorg-website/



Anyway, I did notice something myself, which is that the git logo
links to https://git.postgresql.org/gitweb/, which has many
non-very-important git repos. Instead, it should point to the main
PostgreSQL repository's gitweb page, which is at
https://git.postgresql.org/gitweb/?p=postgresql.git.




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

Re: New website

[Adrian Klaver]

On 04/18/2018 03:06 PM, David G. Johnston wrote:
On Wed, Apr 18, 2018 at 3:03 PM, Adrian Klaver 
>wrote:


I would contact the Webmaster but Contact goes to a big image of an
elephant head. That is also where Downloads, Support and Donate
lands. Might have been a good idea to roll out a demo site for
testing first. Will reserve judgment on the site design until it is
functioning.


This is more appropriately directed to pgsql-www​

I cannot confirm the observed behavior only a couple minutes after 
receiving your initial email.


The links are working now after forcing site reload over a period of time.



David J.




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

Re: New website

[Peter Geoghegan]

On Wed, Apr 18, 2018 at 3:03 PM, Adrian Klaver
 wrote:
> I would contact the Webmaster but Contact goes to a big image of an elephant
> head. That is also where Downloads, Support and Donate lands. Might have
> been a good idea to roll out a demo site for testing first. Will reserve
> judgment on the site design until it is functioning.

I really don't think it's practical to give everyone a veto on a
website design. It took years to launch this website redesign.
Honestly, I was beginning to think that it would never happen.

Anyway, I did notice something myself, which is that the git logo
links to https://git.postgresql.org/gitweb/, which has many
non-very-important git repos. Instead, it should point to the main
PostgreSQL repository's gitweb page, which is at
https://git.postgresql.org/gitweb/?p=postgresql.git.
-- 
Peter Geoghegan

Re: New website

[Don Seiler]

On Wed, Apr 18, 2018 at 5:03 PM, Adrian Klaver 
wrote:

> I would contact the Webmaster but Contact goes to a big image of an
> elephant head. That is also where Downloads, Support and Donate lands.
> Might have been a good idea to roll out a demo site for testing first. Will
> reserve judgment on the site design until it is functioning.


Those links work fine for me on the new site. I haven't hit any issues yet
in my casual click-testing.


-- 
Don Seiler
www.seiler.us

Re: New website

[David G. Johnston]

On Wed, Apr 18, 2018 at 3:03 PM, Adrian Klaver 
wrote:

> I would contact the Webmaster but Contact goes to a big image of an
> elephant head. That is also where Downloads, Support and Donate lands.
> Might have been a good idea to roll out a demo site for testing first. Will
> reserve judgment on the site design until it is functioning.


This is more appropriately directed to pgsql-www​

I cannot confirm the observed behavior only a couple minutes after
receiving your initial email.

David J.

New website

[Adrian Klaver]

I would contact the Webmaster but Contact goes to a big image of an 
elephant head. That is also where Downloads, Support and Donate lands. 
Might have been a good idea to roll out a demo site for testing first. 
Will reserve judgment on the site design until it is functioning.


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

Re: Tricking the optimizer

[Vitaliy Garnashevich]

We've tried to use "... WHERE coalesce(...)::bool;" on a test query:

Before:
->  Seq Scan on public.sys_user sys_user_1  (cost=0.00..27140.46 rows=1 
width=16) (actual time=0.105..38.627 rows=3289 loops=1)
      Filter: (((SubPlan 7) = 0) AND ((SubPlan 8) = 0) AND ((SubPlan 9) 
= 0))


After:
->  Seq Scan on public.sys_user sys_user_1  (cost=0.00..27140.46 
rows=1790 width=16) (actual time=0.136..28.413 rows=3289 loops=1)
      Filter: COALESCESubPlan 7) = 0) AND ((SubPlan 8) = 0) AND 
((SubPlan 9) = 0)))


The estimate got closer to the actual number of rows.

Regards,
Vitaliy

postgres on physical replica crashes

[greigwise]

Hello.  I've had several instances where postgres on my physical replica
under version 9.6.6 is crashing with messages like the following in the
logs:

2018-04-18 05:43:26 UTC dbname 5acf5e4a.6918 dbuser DETAIL:  The postmaster
has commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2018-04-18 05:43:26 UTC dbname 5acf5e4a.6918 dbuser HINT:  In a moment you
should be able to reconnect to the database and repeat your command.
2018-04-18 05:43:26 UTC dbname 5acf5e39.68e5 dbuser WARNING:  terminating
connection because of crash of another server process
2018-04-18 05:43:26 UTC dbname 5acf5e39.68e5 dbuser DETAIL:  The postmaster
has commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2018-04-18 05:43:26 UTC dbname 5acf5e39.68e5 dbuser HINT:  In a moment you
should be able to reconnect to the database and repeat your command.
2018-04-18 05:43:27 UTC  5acf5e12.6819  LOG:  database system is shut down

When this happens, what I've found is that I can go into the pg_xlog
directory on the replica, remove all the log files and the postgres will
restart and things seem to come back up normally.

So, the question is what's going on here... is the log maybe getting corrupt
in transmission somehow?  Should I be concerned about the viability of my
replica after having restarted in the described fashion?

Thanks,
Greig Wise



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

Re: Tricking the optimizer

[Vitaliy Garnashevich]

We'd rather avoid writing C code. We're trying to stick with the DB 
functionality provided by default.


Could something like the following, in theory, make things at least 
somewhat better in our case (without using the selectivity override)?


... WHERE coalesce(securityFilter1 AND securityFilter2 AND 
securityFilter3)::bool;


Regards,
Vitaliy

Re: pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

[Adrian Klaver]

On 04/18/2018 08:04 AM, Pavel Raiskup wrote:

On Wednesday, April 18, 2018 4:43:01 PM CEST Adrian Klaver wrote:



I am obviously missing something. If the old server was using hstore in
a database how could hstore.so could be accessible to it but not pg_dump?


Because on Fedora we usually run pg_upgrade after distribution upgrade
(e.g. for Fedora 27 => Fedora 28 upgrade it means also upgrade from PostgreSQL
9.6 to 10), and then we provide the old server in different package
(postgresql-upgrade) which has limited feature set (including the missing
hstore.so module).


Thanks, now it makes sense.



Pavel



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

Re: pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

[Pavel Raiskup]

On Wednesday, April 18, 2018 4:43:01 PM CEST Adrian Klaver wrote:
> On 04/18/2018 07:22 AM, Tom Lane wrote:
> > Pavel Raiskup  writes:
> >> . and it seems like the hstore.so was somewhat intimately integrated into
> >> OP's database so the '/usr/bin/pg_dump --schema-only --binary-upgrade
> >> --format=custom' called through 'pg_upgrade' failed with:
> >>pg_dump: [archiver (db)] query failed: ERROR:  could not access file
> >>"$libdir/hstore": No such file or directory
> >> Which means that the dump from old datadir, with old server (without
> >> hstore.so packaged) failed.  But playing with hstore.so a bit, the upgrade
> >> always worked smoothly for me even without the "old" hstore.so
> > 
> > Hi Pavel,
> > 
> > There are certainly plenty of reasons why extension .so's might be needed
> > during pg_dump, even in a binary-upgrade situation.  The first example
> > that comes to mind is that an hstore-type constant appearing in a view
> > definition would require hstore_out() to be invoked while dumping the view
> > definition.
> 
> I am obviously missing something. If the old server was using hstore in 
> a database how could hstore.so could be accessible to it but not pg_dump?

Because on Fedora we usually run pg_upgrade after distribution upgrade
(e.g. for Fedora 27 => Fedora 28 upgrade it means also upgrade from PostgreSQL
9.6 to 10), and then we provide the old server in different package
(postgresql-upgrade) which has limited feature set (including the missing
hstore.so module).

Pavel

> > 
> > I don't remember anymore whether I'd set up the postgresql-update package
> > to include the contrib modules for the old server version.  If I didn't,
> > it was an oversight :-(.
> > 
> > regards, tom lane
> > 
> > 
> 
> 
> 

Re: pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

[Pavel Raiskup]

On Wednesday, April 18, 2018 4:22:23 PM CEST Tom Lane wrote:
> Pavel Raiskup  writes:
> > [2] https://bugzilla.redhat.com/show_bug.cgi?id=1557490
>
> There are certainly plenty of reasons why extension .so's might be needed
> during pg_dump, even in a binary-upgrade situation.  The first example
> that comes to mind is that an hstore-type constant appearing in a view
> definition would require hstore_out() to be invoked while dumping the view
> definition.

Thanks for the example, Tom.  I'll resolve the bug [2] then.

Pavel

Re: pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

[Adrian Klaver]

On 04/18/2018 07:22 AM, Tom Lane wrote:

Pavel Raiskup  writes:

. and it seems like the hstore.so was somewhat intimately integrated into
OP's database so the '/usr/bin/pg_dump --schema-only --binary-upgrade
--format=custom' called through 'pg_upgrade' failed with:
   pg_dump: [archiver (db)] query failed: ERROR:  could not access file
   "$libdir/hstore": No such file or directory
Which means that the dump from old datadir, with old server (without
hstore.so packaged) failed.  But playing with hstore.so a bit, the upgrade
always worked smoothly for me even without the "old" hstore.so


Hi Pavel,

There are certainly plenty of reasons why extension .so's might be needed
during pg_dump, even in a binary-upgrade situation.  The first example
that comes to mind is that an hstore-type constant appearing in a view
definition would require hstore_out() to be invoked while dumping the view
definition.


I am obviously missing something. If the old server was using hstore in 
a database how could hstore.so could be accessible to it but not pg_dump?




I don't remember anymore whether I'd set up the postgresql-update package
to include the contrib modules for the old server version.  If I didn't,
it was an oversight :-(.

regards, tom lane





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

Re: pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

[Tom Lane]

Pavel Raiskup  writes:
> . and it seems like the hstore.so was somewhat intimately integrated into
> OP's database so the '/usr/bin/pg_dump --schema-only --binary-upgrade
> --format=custom' called through 'pg_upgrade' failed with:
>   pg_dump: [archiver (db)] query failed: ERROR:  could not access file
>   "$libdir/hstore": No such file or directory
> Which means that the dump from old datadir, with old server (without
> hstore.so packaged) failed.  But playing with hstore.so a bit, the upgrade
> always worked smoothly for me even without the "old" hstore.so

Hi Pavel,

There are certainly plenty of reasons why extension .so's might be needed
during pg_dump, even in a binary-upgrade situation.  The first example
that comes to mind is that an hstore-type constant appearing in a view
definition would require hstore_out() to be invoked while dumping the view
definition.

I don't remember anymore whether I'd set up the postgresql-update package
to include the contrib modules for the old server version.  If I didn't,
it was an oversight :-(.

regards, tom lane

Re: pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

[Adrian Klaver]

On 04/18/2018 07:07 AM, Pavel Raiskup wrote:

Hi all,

with a huge delay, I've seen this question [1].

. and it seems like the hstore.so was somewhat intimately integrated into
OP's database so the '/usr/bin/pg_dump --schema-only --binary-upgrade
--format=custom' called through 'pg_upgrade' failed with:

   pg_dump: [archiver (db)] query failed: ERROR:  could not access file
   "$libdir/hstore": No such file or directory


From [1] it seems there is confusion in the system about where $libdir 
is. Did anyone run pg_config to try to track this down?




Which means that the dump from old datadir, with old server (without
hstore.so packaged) failed.  But playing with hstore.so a bit, the upgrade
always worked smoothly for me even without the "old" hstore.so


I am not following the above. If you played with hstore.so how could it 
not be around? Or does this have something to do with 'old'?




Googling around, I only found that there were some incompatible changes
historically in hstore.so causing pg_upgrade failure;  but this is
different issue -- in the issue I talk about now the module was not found
at all, even though it is usually even not searched for.

So the question is what circumstances could cause this issue in
particular?  Is it expected in general that, to make the pg_upgrade run
smooth, all the "loaded" server modules are built against and available
for the --old-bindir server?  Don't we have some reproducer for this
kind of issues?

[1] 
https://ask.fedoraproject.org/en/question/69316/postgresql-doesnt-work-after-upgrade-to-f22/
[2] https://bugzilla.redhat.com/show_bug.cgi?id=1557490

Thank for hints,
Pavel








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

pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

[Pavel Raiskup]

Hi all,

with a huge delay, I've seen this question [1].

. and it seems like the hstore.so was somewhat intimately integrated into
OP's database so the '/usr/bin/pg_dump --schema-only --binary-upgrade
--format=custom' called through 'pg_upgrade' failed with:

  pg_dump: [archiver (db)] query failed: ERROR:  could not access file
  "$libdir/hstore": No such file or directory

Which means that the dump from old datadir, with old server (without
hstore.so packaged) failed.  But playing with hstore.so a bit, the upgrade
always worked smoothly for me even without the "old" hstore.so

Googling around, I only found that there were some incompatible changes
historically in hstore.so causing pg_upgrade failure;  but this is
different issue -- in the issue I talk about now the module was not found
at all, even though it is usually even not searched for.

So the question is what circumstances could cause this issue in
particular?  Is it expected in general that, to make the pg_upgrade run
smooth, all the "loaded" server modules are built against and available
for the --old-bindir server?  Don't we have some reproducer for this
kind of issues?

[1] 
https://ask.fedoraproject.org/en/question/69316/postgresql-doesnt-work-after-upgrade-to-f22/
[2] https://bugzilla.redhat.com/show_bug.cgi?id=1557490 

Thank for hints,
Pavel

Re: pg_upgrade help

[Adrian Klaver]

On 04/18/2018 05:02 AM, Akshay Ballarpure wrote:

Hi Fabio,
sorry to bother you again, its still failing with stopping both server 
(8.4 and 9.4)


Actually according to the command show at bottom of post it is failing 
trying to start the 8.4 server. In your previous post that was because 
it was already running:


-bash-4.2$ ps -eaf | grep postgres
postgres  9778 1  0 09:17 ?00:00:00 /usr/bin/postgres -p 
50432 -D /var/ericsson/esm-data/postgresql-data/


FATAL:  lock file "postmaster.pid" already exists
HINT:  Is another postmaster (PID 9778) running in data directory 
"/var/ericsson/esm-data/postgresql-data"?

pg_ctl: could not start server


Make sure both servers are stopped before running pg_upgrade. Per a 
previous suggestion follow the check list here:


https://www.postgresql.org/docs/10/static/pgupgrade.html
"
Usage

These are the steps to perform an upgrade with pg_upgrade:

...

"



-bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade 
--old-bindir=/usr/bin --new-bindir=/opt/rh/rh-postgresql94/root/usr/bin 
--old-datadir=$OLDCLUSTER --new-datadir=$NEWCLUSTER


connection to database failed: could not connect to server: No such file 
or directory

         Is the server running locally and accepting
         connections on Unix domain socket 
"/var/run/postgresql/.s.PGSQL.50432"?



could not connect to old postmaster started with the command:
"/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D 
"/var/ericsson/esm-data/postgresql-data" -o "-p 50432 -c autovacuum=off 
-c autovacuum_freeze_max_age=20  -c listen_addresses='' -c 
unix_socket_permissions=0700" start

Failure, exiting


With Best Regards
Akshay




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

Re: How to fetch data from tables in PostgreSQL

[Melvin Davidson]

On Wed, Apr 18, 2018 at 9:48 AM, Adrian Klaver 
wrote:

> On 04/18/2018 12:38 AM, vaibhav zaveri wrote:
>
>> Hi,
>>
>> Thanks for your reply
>> Which are the important tables from which we need to get data?
>>
>
> Important for what?
>
>>
>> Regards,
>> Vaibhav Zaveri
>>
>> On 18 Apr 2018 12:52, "vaibhav zaveri"  vaibhavzave...@gmail.com>> wrote:
>>
>> Hi,
>>
>> How to fetch data from tables in PostgreSQL.
>>
>> Regards,
>> Vaibhav Zaveri
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>


*>Which are the important tables from which we need to get data? *

*The whole idea of a relational database such as PostgreSQL, is that YOU
create the schema/tables that are important to you and you store *

*and retrieve the information that is important to you. When PostgreSQL is
first installed it cannot possibly have any tables/information that you*

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

Re: Tricking the optimizer

[Tom Lane]

Vitaliy Garnashevich  writes:
> Is there any trick to craft the query in such a way, so that to make the 
> optimizer believe that the filters would not remove any rows, and all 
> rows will likely be returned by the query?

If you don't mind writing some C code, you could create a dummy operator
that just returns its boolean argument, and attach a selectivity estimator
to it that returns 1.0.  Then you'd write the query as, perhaps,

WHERE ~~~(securityFilter1) AND ...

where ~~~ could be read as "likely()".  I wouldn't recommend using such a
wrapper for any WHERE clause that the optimizer had any intelligence about
at all, because it would defeat all of that.  But in this case you're
not getting any wins anyhow, so burying an opaque subselect in another
layer of opacity won't hurt.

regards, tom lane

Re: How to fetch data from tables in PostgreSQL

[Adrian Klaver]

On 04/18/2018 12:38 AM, vaibhav zaveri wrote:

Hi,

Thanks for your reply
Which are the important tables from which we need to get data?


Important for what?


Regards,
Vaibhav Zaveri

On 18 Apr 2018 12:52, "vaibhav zaveri" > wrote:


Hi,

How to fetch data from tables in PostgreSQL.

Regards,
Vaibhav Zaveri




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

Re: Can PostgreSQL create new WAL files instead of reusing old ones?

[Vick Khera]

On Wed, Apr 18, 2018 at 12:35 AM, Michael Paquier 
wrote:

>
> That looks like a rather difficult problem to solve in PostgreSQL
> itself, as the operator running the cluster is in charge of setting up
> the FS options which would control the COW behavior, so it seems to me
>

You cannot turn off CoW on ZFS. What other behavior would you refer to here?

I suppose one could make a dedicated data set for the WAL and have ZFS make
a reservation for about 2x the total expected WAL size. It would require
careful attention to detail if you increase WAL segments configuration,
though, and if you had any kind of hiccup with streaming replication that
caused the segments to stick around longer than expected (but that's no
different from any other file system).

Re: pg_upgrade help

[Fabio Pardi]

did you run initdb on the new db?

what happens if you manually start the new db?

/opt/rh/rh-postgresql94/root/usr/bin/pg_ctl  start -o "-p 50432 -c
listen_addresses='' -c unix_socket_permissions=0700"  -D $NEWCLUSTER

after starting it, can you connect to it using psql?

psql -p 50432 -h /var/run/postgresql  -U your_user _db_



regards,

fabio pardi


On 04/18/2018 02:02 PM, Akshay Ballarpure wrote:
> Hi Fabio,
> sorry to bother you again, its still failing with stopping both server
> (8.4 and 9.4)
> 
> -bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade
> --old-bindir=/usr/bin --new-bindir=/opt/rh/rh-postgresql94/root/usr/bin
> --old-datadir=$OLDCLUSTER --new-datadir=$NEWCLUSTER
> 
> connection to database failed: could not connect to server: No such file
> or directory
>         Is the server running locally and accepting
>         connections on Unix domain socket
> "/var/run/postgresql/.s.PGSQL.50432"?
> 
> 
> could not connect to old postmaster started with the command:
> "/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
> "/var/ericsson/esm-data/postgresql-data" -o "-p 50432 -c autovacuum=off
> -c autovacuum_freeze_max_age=20  -c listen_addresses='' -c
> unix_socket_permissions=0700" start
> Failure, exiting
> 
> 
> With Best Regards
> Akshay
> Ericsson OSS MON
> Tata Consultancy Services
> Mailto: akshay.ballarp...@tcs.com
> Website: http://www.tcs.com 
> 
> Experience certainty.        IT Services
>                        Business Solutions
>                        Consulting
> 
> 
> 
> 
> 
> From:        Fabio Pardi 
> To:        Akshay Ballarpure ,
> pgsql-general@lists.postgresql.org
> Date:        04/18/2018 02:35 PM
> Subject:        Re: pg_upgrade help
> 
> 
> 
> 
> Hi,
> 
> i was too fast in reply (and perhaps i should drink my morning coffee
> before replying), I will try to be more detailed:
> 
> both servers should be able to run at the moment you run pg_upgrade,
> that means the 2 servers should have been correctly stopped in advance,
> should have their configuration files, and new cluster initialized too.
> 
> Then, as Sergei highlights here below, pg_upgrade will take care of the
> upgrade process, starting the servers.
> 
> 
> Here there is a step by step guide, i considered my best ally when it
> was time to upgrade:
> 
> https://www.postgresql.org/docs/9.4/static/pgupgrade.html
> 
> note point 7:
> 
> 'stop both servers'
> 
> 
> About the port the servers will run on, at point 9 there is some
> clarification:
> 
> ' pg_upgrade defaults to running servers on port 50432 to avoid
> unintended client connections. You can use the same port number for both
> clusters when doing an upgrade because the old and new clusters will not
> be running at the same time. However, when checking an old running
> server, the old and new port numbers must be different.'
> 
> Hope it helps,
> 
> Fabio Pardi
> 
> 
> On 04/18/2018 10:34 AM, Akshay Ballarpure wrote:
>> Thanks Fabio for instant reply.
>>
>> I now started 8.4 with 50432 and 9.4 with default port but still its
>> failing ...Can you please suggest what is wrong ?
>>
>> -bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade
>> --old-bindir=/usr/bin --new-bindir=/opt/rh/rh-postgresql94/root/usr/bin
>> --old-datadir=$OLDCLUSTER --new-datadir=$NEWCLUSTER
>>
>> *failure*
>> Consult the last few lines of "pg_upgrade_server.log" for
>> the probable cause of the failure.
>>
>> There seems to be a postmaster servicing the old cluster.
>> Please shutdown that postmaster and try again.
>> Failure, exiting
>> -bash-4.2$ ps -eaf | grep postgres
>> root      8646  9365  0 08:07 pts/1    00:00:00 su - postgres
>> postgres  8647  8646  0 08:07 pts/1    00:00:00 -bash
>> postgres  9778     1  0 09:17 ?        00:00:00 /usr/bin/postgres -p
>> 50432 -D /var/ericsson/esm-data/postgresql-data/
>> postgres  9779  9778  0 09:17 ?        00:00:00 postgres: logger process
>> postgres  9781  9778  0 09:17 ?        00:00:00 postgres: writer process
>> postgres  9782  9778  0 09:17 ?        00:00:00 postgres: wal writer
>> process
>> postgres  9783  9778  0 09:17 ?        00:00:00 postgres: autovacuum
>> launcher process
>> postgres  9784  9778  0 09:17 ?        00:00:00 postgres: stats
>> collector process
>> postgres  9900     1  0 09:20 ?        00:00:00
>> /opt/rh/rh-postgresql94/root/usr/bin/postgres -D
>> /var/ericsson/esm-data/postgresql-data-9.4/
>> postgres  9901  9900  0 09:20 ?        00:00:00 postgres: logger process
>> postgres  9903  9900  0 09:20 ?        00:00:00 postgres: checkpointer
>> process
>> postgres  9904  9900  0 09:20 ?        00:00:00 postgres: writer process
>> postgres  9905  9900  0 09:20 ?        00:00:00 postgres: wal writer
>> process
>> postgres  9906  9900  0 09:20 ?        00:00:00 

Re: pg_locks.relation question

[Daniel Westermann]

On Wed, Apr 18, 2018 at 5:35 PM, Daniel Westermann < [ 
mailto:daniel.westerm...@dbi-services.com | daniel.westerm...@dbi-services.com 
] > wrote: 



Hi, 

session one: 

locks=# begin; 
BEGIN 
locks=# alter table test add column b text; 
ALTER TABLE 
locks=# 

Session 2 querying pg_locks for the PID from above: 

locks=# select locktype,relation::regclass,mode from pg_locks where pid = 2026 
and locktype = 'relation'; 
locktype | relation | mode 
--+--+- 
relation | test | AccessExclusiveLock 
relation | 17728 | AccessExclusiveLock 
relation | 17726 | ShareLock 
(3 rows) 

What are these 17728 and 17726 OIDs? Nothing else is happening except these two 
sessions. 




>>Those are probably the OIDs of the toast table and the index on it. If you 
>>commit the transaction running in Session 1 and then query >>pg_class for 
>>those OIDs, you might see more details. Of course, I am just guessing without 
>>looking into much detail. 

Thx, Pavan, that's it. 

Re: pg_locks.relation question

[Pavan Deolasee]

On Wed, Apr 18, 2018 at 5:35 PM, Daniel Westermann <
daniel.westerm...@dbi-services.com> wrote:

> Hi,
>
> session one:
>
> locks=# begin;
> BEGIN
> locks=# alter table test add column b text;
> ALTER TABLE
> locks=#
>
> Session 2 querying pg_locks for the PID from above:
>
> locks=# select locktype,relation::regclass,mode from pg_locks where pid =
> 2026 and locktype = 'relation';
>  locktype | relation |mode
> --+--+-
>  relation | test | AccessExclusiveLock
>  relation | 17728| AccessExclusiveLock
>  relation | 17726| ShareLock
> (3 rows)
>
> What are these 17728 and 17726 OIDs? Nothing else is happening except
> these two sessions.
>
>
Those are probably the OIDs of the toast table and the index on it. If you
commit the transaction running in Session 1 and then query pg_class for
those OIDs, you might see more details. Of course, I am just guessing
without looking into much detail.

Thanks,
Pavan

-- 
 Pavan Deolasee   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Tricking the optimizer

[Vitaliy Garnashevich]

Hi,

In order to implement some security features in our application, we 
sometimes append additional WHERE filters to our queries, so the filer 
in the end looks like:


SELECT ... FROM ... WHERE securityFilter1 AND securityFilter2 AND 
securityFilter3


In the EXPLAIN the filters look something like:

(((SubPlan 1) = 'foo') AND ((SubPlan 2) = 'bar') AND ((SubPlan 3) = 'baz'))

There are no applicable indexes and each filter clause looks opaque to 
the optimizer, so the optimizer is using some generic rules and assumes 
that each AND clause would reduce the total number of returned rows by 
some factor. The problem is that this is not usually the case, and 
usually the clauses would not filter out any rows at all. This leads to 
poor plans, when such query is a part of another bigger query.


Is there any trick to craft the query in such a way, so that to make the 
optimizer believe that the filters would not remove any rows, and all 
rows will likely be returned by the query?


Regards,
Vitaliy

pg_locks.relation question

[Daniel Westermann]

Hi, 

session one: 

locks=# begin; 
BEGIN 
locks=# alter table test add column b text; 
ALTER TABLE 
locks=# 

Session 2 querying pg_locks for the PID from above: 

locks=# select locktype,relation::regclass,mode from pg_locks where pid = 2026 
and locktype = 'relation'; 
locktype | relation | mode 
--+--+- 
relation | test | AccessExclusiveLock 
relation | 17728 | AccessExclusiveLock 
relation | 17726 | ShareLock 
(3 rows) 

What are these 17728 and 17726 OIDs? Nothing else is happening except these two 
sessions. 

Thanks in advance 
Daniel 

Re: pg_upgrade help

[Akshay Ballarpure]

Hi Fabio,
sorry to bother you again, its still failing with stopping both server 
(8.4 and 9.4)

-bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade 
--old-bindir=/usr/bin --new-bindir=/opt/rh/rh-postgresql94/root/usr/bin 
--old-datadir=$OLDCLUSTER --new-datadir=$NEWCLUSTER

connection to database failed: could not connect to server: No such file 
or directory
Is the server running locally and accepting
connections on Unix domain socket 
"/var/run/postgresql/.s.PGSQL.50432"?


could not connect to old postmaster started with the command:
"/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D 
"/var/ericsson/esm-data/postgresql-data" -o "-p 50432 -c autovacuum=off -c 
autovacuum_freeze_max_age=20  -c listen_addresses='' -c 
unix_socket_permissions=0700" start
Failure, exiting


With Best Regards
Akshay
Ericsson OSS MON
Tata Consultancy Services
Mailto: akshay.ballarp...@tcs.com
Website: http://www.tcs.com

Experience certainty.   IT Services
Business Solutions
Consulting





From:   Fabio Pardi 
To: Akshay Ballarpure , 
pgsql-general@lists.postgresql.org
Date:   04/18/2018 02:35 PM
Subject:Re: pg_upgrade help



Hi,

i was too fast in reply (and perhaps i should drink my morning coffee
before replying), I will try to be more detailed:

both servers should be able to run at the moment you run pg_upgrade,
that means the 2 servers should have been correctly stopped in advance,
should have their configuration files, and new cluster initialized too.

Then, as Sergei highlights here below, pg_upgrade will take care of the
upgrade process, starting the servers.


Here there is a step by step guide, i considered my best ally when it
was time to upgrade:

https://www.postgresql.org/docs/9.4/static/pgupgrade.html

note point 7:

'stop both servers'


About the port the servers will run on, at point 9 there is some
clarification:

' pg_upgrade defaults to running servers on port 50432 to avoid
unintended client connections. You can use the same port number for both
clusters when doing an upgrade because the old and new clusters will not
be running at the same time. However, when checking an old running
server, the old and new port numbers must be different.'

Hope it helps,

Fabio Pardi


On 04/18/2018 10:34 AM, Akshay Ballarpure wrote:
> Thanks Fabio for instant reply.
> 
> I now started 8.4 with 50432 and 9.4 with default port but still its
> failing ...Can you please suggest what is wrong ?
> 
> -bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade
> --old-bindir=/usr/bin --new-bindir=/opt/rh/rh-postgresql94/root/usr/bin
> --old-datadir=$OLDCLUSTER --new-datadir=$NEWCLUSTER
> 
> *failure*
> Consult the last few lines of "pg_upgrade_server.log" for
> the probable cause of the failure.
> 
> There seems to be a postmaster servicing the old cluster.
> Please shutdown that postmaster and try again.
> Failure, exiting
> -bash-4.2$ ps -eaf | grep postgres
> root  8646  9365  0 08:07 pts/100:00:00 su - postgres
> postgres  8647  8646  0 08:07 pts/100:00:00 -bash
> postgres  9778 1  0 09:17 ?00:00:00 /usr/bin/postgres -p
> 50432 -D /var/ericsson/esm-data/postgresql-data/
> postgres  9779  9778  0 09:17 ?00:00:00 postgres: logger process
> postgres  9781  9778  0 09:17 ?00:00:00 postgres: writer process
> postgres  9782  9778  0 09:17 ?00:00:00 postgres: wal writer
> process
> postgres  9783  9778  0 09:17 ?00:00:00 postgres: autovacuum
> launcher process
> postgres  9784  9778  0 09:17 ?00:00:00 postgres: stats
> collector process
> postgres  9900 1  0 09:20 ?00:00:00
> /opt/rh/rh-postgresql94/root/usr/bin/postgres -D
> /var/ericsson/esm-data/postgresql-data-9.4/
> postgres  9901  9900  0 09:20 ?00:00:00 postgres: logger process
> postgres  9903  9900  0 09:20 ?00:00:00 postgres: checkpointer
> process
> postgres  9904  9900  0 09:20 ?00:00:00 postgres: writer process
> postgres  9905  9900  0 09:20 ?00:00:00 postgres: wal writer
> process
> postgres  9906  9900  0 09:20 ?00:00:00 postgres: autovacuum
> launcher process
> postgres  9907  9900  0 09:20 ?00:00:00 postgres: stats
> collector process
> postgres  9926  8647  0 09:21 pts/100:00:00 ps -eaf
> postgres  9927  8647  0 09:21 pts/100:00:00 grep --color=auto 
postgres
> 
> 
> -bash-4.2$ netstat -antp | grep 50432
> (Not all processes could be identified, non-owned process info
>  will not be shown, you would have to be root to see it all.)
> tcp0  0 127.0.0.1:50432 0.0.0.0:*  
> LISTEN  9778/postgres
> tcp6   0  0 ::1:50432   :::*  
>  LISTEN  9778/postgres
> -bash-4.2$ netstat -antp | grep 5432
> (Not all processes could be identified, 

Re: pg_upgrade help

[Fabio Pardi]

Hi,

i was too fast in reply (and perhaps i should drink my morning coffee
before replying), I will try to be more detailed:

both servers should be able to run at the moment you run pg_upgrade,
that means the 2 servers should have been correctly stopped in advance,
should have their configuration files, and new cluster initialized too.

Then, as Sergei highlights here below, pg_upgrade will take care of the
upgrade process, starting the servers.


Here there is a step by step guide, i considered my best ally when it
was time to upgrade:

https://www.postgresql.org/docs/9.4/static/pgupgrade.html

note point 7:

'stop both servers'


About the port the servers will run on, at point 9 there is some
clarification:

' pg_upgrade defaults to running servers on port 50432 to avoid
unintended client connections. You can use the same port number for both
clusters when doing an upgrade because the old and new clusters will not
be running at the same time. However, when checking an old running
server, the old and new port numbers must be different.'

Hope it helps,

Fabio Pardi


On 04/18/2018 10:34 AM, Akshay Ballarpure wrote:
> Thanks Fabio for instant reply.
> 
> I now started 8.4 with 50432 and 9.4 with default port but still its
> failing ...Can you please suggest what is wrong ?
> 
> -bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade
> --old-bindir=/usr/bin --new-bindir=/opt/rh/rh-postgresql94/root/usr/bin
> --old-datadir=$OLDCLUSTER --new-datadir=$NEWCLUSTER
> 
> *failure*
> Consult the last few lines of "pg_upgrade_server.log" for
> the probable cause of the failure.
> 
> There seems to be a postmaster servicing the old cluster.
> Please shutdown that postmaster and try again.
> Failure, exiting
> -bash-4.2$ ps -eaf | grep postgres
> root      8646  9365  0 08:07 pts/1    00:00:00 su - postgres
> postgres  8647  8646  0 08:07 pts/1    00:00:00 -bash
> postgres  9778     1  0 09:17 ?        00:00:00 /usr/bin/postgres -p
> 50432 -D /var/ericsson/esm-data/postgresql-data/
> postgres  9779  9778  0 09:17 ?        00:00:00 postgres: logger process
> postgres  9781  9778  0 09:17 ?        00:00:00 postgres: writer process
> postgres  9782  9778  0 09:17 ?        00:00:00 postgres: wal writer
> process
> postgres  9783  9778  0 09:17 ?        00:00:00 postgres: autovacuum
> launcher process
> postgres  9784  9778  0 09:17 ?        00:00:00 postgres: stats
> collector process
> postgres  9900     1  0 09:20 ?        00:00:00
> /opt/rh/rh-postgresql94/root/usr/bin/postgres -D
> /var/ericsson/esm-data/postgresql-data-9.4/
> postgres  9901  9900  0 09:20 ?        00:00:00 postgres: logger process
> postgres  9903  9900  0 09:20 ?        00:00:00 postgres: checkpointer
> process
> postgres  9904  9900  0 09:20 ?        00:00:00 postgres: writer process
> postgres  9905  9900  0 09:20 ?        00:00:00 postgres: wal writer
> process
> postgres  9906  9900  0 09:20 ?        00:00:00 postgres: autovacuum
> launcher process
> postgres  9907  9900  0 09:20 ?        00:00:00 postgres: stats
> collector process
> postgres  9926  8647  0 09:21 pts/1    00:00:00 ps -eaf
> postgres  9927  8647  0 09:21 pts/1    00:00:00 grep --color=auto postgres
> 
> 
> -bash-4.2$ netstat -antp | grep 50432
> (Not all processes could be identified, non-owned process info
>  will not be shown, you would have to be root to see it all.)
> tcp        0      0 127.0.0.1:50432         0.0.0.0:*              
> LISTEN      9778/postgres
> tcp6       0      0 ::1:50432               :::*                  
>  LISTEN      9778/postgres
> -bash-4.2$ netstat -antp | grep 5432
> (Not all processes could be identified, non-owned process info
>  will not be shown, you would have to be root to see it all.)
> tcp        0      0 127.0.0.1:5432          0.0.0.0:*              
> LISTEN      9900/postgres
> tcp6       0      0 ::1:5432                :::*                  
>  LISTEN      9900/postgres
> 
> -
>   pg_upgrade run on Wed Apr 18 09:24:47 2018
> -
> 
> command: "/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
> "/var/ericsson/esm-data/postgresql-data" -o "-p 50432 -c autovacuum=off
> -c autovacuum_freeze_max_age=20  -c listen_addresses='' -c
> unix_socket_permissions=0700" start >> "pg_upgrade_server.log" 2>&1
> pg_ctl: another server might be running; trying to start server anyway
> FATAL:  lock file "postmaster.pid" already exists
> HINT:  Is another postmaster (PID 9778) running in data directory
> "/var/ericsson/esm-data/postgresql-data"?
> pg_ctl: could not start server
> Examine the log output.
> 
> 
> [root@ms-esmon /]# cat
> ./var/ericsson/esm-data/postgresql-data-9.4/postmaster.pid
> 9900
> /var/ericsson/esm-data/postgresql-data-9.4
> 1524039630
> 5432
> /var/run/postgresql
> localhost
>   5432001   2031616
>  
>  
> [root@ms-esmon /]# cat
> ./var/ericsson/esm-data/postgresql-data/postmaster.pid
> 

Re: pg_upgrade help

[Sergei Kornilov]

Hi
Both version should be correctly stopped. pg_upgrade started clusters itself.
Please check pg_upgrade_server.log file in directory where pg_upgrade was run.
Also where is postgresql.conf? In PGDATA? Otherwise you need tell pg_upgrade 
correct path, for example with options '-o " -c 
config_file=/etc/postgresql/8.4/main/postgresql.conf" -O " -c 
config_file=/etc/postgresql/9.4/main/postgresql.conf"'

regards, Sergei

Re: pg_upgrade help

[Akshay Ballarpure]

Thanks Fabio for instant reply.

I now started 8.4 with 50432 and 9.4 with default port but still its 
failing ...Can you please suggest what is wrong ?

-bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade 
--old-bindir=/usr/bin --new-bindir=/opt/rh/rh-postgresql94/root/usr/bin 
--old-datadir=$OLDCLUSTER --new-datadir=$NEWCLUSTER

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.
Failure, exiting
-bash-4.2$ ps -eaf | grep postgres
root  8646  9365  0 08:07 pts/100:00:00 su - postgres
postgres  8647  8646  0 08:07 pts/100:00:00 -bash
postgres  9778 1  0 09:17 ?00:00:00 /usr/bin/postgres -p 50432 
-D /var/ericsson/esm-data/postgresql-data/
postgres  9779  9778  0 09:17 ?00:00:00 postgres: logger process
postgres  9781  9778  0 09:17 ?00:00:00 postgres: writer process
postgres  9782  9778  0 09:17 ?00:00:00 postgres: wal writer 
process
postgres  9783  9778  0 09:17 ?00:00:00 postgres: autovacuum 
launcher process
postgres  9784  9778  0 09:17 ?00:00:00 postgres: stats collector 
process
postgres  9900 1  0 09:20 ?00:00:00 
/opt/rh/rh-postgresql94/root/usr/bin/postgres -D 
/var/ericsson/esm-data/postgresql-data-9.4/
postgres  9901  9900  0 09:20 ?00:00:00 postgres: logger process
postgres  9903  9900  0 09:20 ?00:00:00 postgres: checkpointer 
process
postgres  9904  9900  0 09:20 ?00:00:00 postgres: writer process
postgres  9905  9900  0 09:20 ?00:00:00 postgres: wal writer 
process
postgres  9906  9900  0 09:20 ?00:00:00 postgres: autovacuum 
launcher process
postgres  9907  9900  0 09:20 ?00:00:00 postgres: stats collector 
process
postgres  9926  8647  0 09:21 pts/100:00:00 ps -eaf
postgres  9927  8647  0 09:21 pts/100:00:00 grep --color=auto postgres


-bash-4.2$ netstat -antp | grep 50432
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp0  0 127.0.0.1:50432 0.0.0.0:*   LISTEN 
 9778/postgres
tcp6   0  0 ::1:50432   :::*LISTEN 
 9778/postgres
-bash-4.2$ netstat -antp | grep 5432
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp0  0 127.0.0.1:5432  0.0.0.0:*   LISTEN 
 9900/postgres
tcp6   0  0 ::1:5432:::*LISTEN 
 9900/postgres

-
  pg_upgrade run on Wed Apr 18 09:24:47 2018
-

command: "/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D 
"/var/ericsson/esm-data/postgresql-data" -o "-p 50432 -c autovacuum=off -c 
autovacuum_freeze_max_age=20  -c listen_addresses='' -c 
unix_socket_permissions=0700" start >> "pg_upgrade_server.log" 2>&1
pg_ctl: another server might be running; trying to start server anyway
FATAL:  lock file "postmaster.pid" already exists
HINT:  Is another postmaster (PID 9778) running in data directory 
"/var/ericsson/esm-data/postgresql-data"?
pg_ctl: could not start server
Examine the log output.


[root@ms-esmon /]# cat 
./var/ericsson/esm-data/postgresql-data-9.4/postmaster.pid
9900
/var/ericsson/esm-data/postgresql-data-9.4
1524039630
5432
/var/run/postgresql
localhost
  5432001   2031616
 
 
[root@ms-esmon /]# cat 
./var/ericsson/esm-data/postgresql-data/postmaster.pid
9778
/var/ericsson/esm-data/postgresql-data
 50432001   1998850




With Best Regards
Akshay





From:   Fabio Pardi 
To: Akshay Ballarpure , 
pgsql-general@lists.postgresql.org
Date:   04/18/2018 01:06 PM
Subject:Re: pg_upgrade help



Hi,

please avoid crossposting to multiple mailing lists.


You need to run both versions of the database, the old and the new.

They need to run on different ports (note that it is impossible to run 2
different processes on the same port, that's not a postgresql thing)



On 04/18/2018 09:30 AM, Akshay Ballarpure wrote:
> Hi all,
> I need help on pg_upgrade from 8.4 to 9.4 version. Appreciate urgent
> response.
> Installed both version and stopped it. Do i need to run both version or
> only one 8.4 or 9.4 . Both should run on 50432 ?
> 
> 
> -bash-4.2$ id
> uid=26(postgres) gid=26(postgres) groups=26(postgres)
> context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
> 
> -bash-4.2$ export OLDCLUSTER=/var/ericsson/esm-data/postgresql-data
>-- 8.4 data
> -bash-4.2$ export NEWCLUSTER=/var/ericsson/esm-data/postgresql-data-9.4
>   -- 9.4 data
> 
> 
> -bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade
> --old-bindir=/usr/bin 

Re: How to fetch data from tables in PostgreSQL

[vaibhav zaveri]

Hi,

Thanks for your reply
Which are the important tables from which we need to get data?

Regards,
Vaibhav Zaveri

On 18 Apr 2018 12:52, "vaibhav zaveri"  wrote:

> Hi,
>
> How to fetch data from tables in PostgreSQL.
>
> Regards,
> Vaibhav Zaveri
>

Re: pg_upgrade help

[Fabio Pardi]

Hi,

please avoid crossposting to multiple mailing lists.


You need to run both versions of the database, the old and the new.

They need to run on different ports (note that it is impossible to run 2
different processes on the same port, that's not a postgresql thing)



On 04/18/2018 09:30 AM, Akshay Ballarpure wrote:
> Hi all,
> I need help on pg_upgrade from 8.4 to 9.4 version. Appreciate urgent
> response.
> Installed both version and stopped it. Do i need to run both version or
> only one 8.4 or 9.4 . Both should run on 50432 ?
> 
> 
> -bash-4.2$ id
> uid=26(postgres) gid=26(postgres) groups=26(postgres)
> context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
> 
> -bash-4.2$ export OLDCLUSTER=/var/ericsson/esm-data/postgresql-data    
>                        -- 8.4 data
> -bash-4.2$ export NEWCLUSTER=/var/ericsson/esm-data/postgresql-data-9.4
>                   -- 9.4 data
> 
> 
> -bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade
> --old-bindir=/usr/bin --new-bindir=/opt/rh/rh-postgresql94/root/usr/bin
> --old-datadir=$OLDCLUSTER --new-datadir=$NEWCLUSTER
> 
> *connection to database failed: could not connect to server: No such
> file or directory*
>         Is the server running locally and accepting
>         connections on Unix domain socket
> "/var/run/postgresql/.s.PGSQL.50432"?
> 
> 
> could not connect to old postmaster started with the command:
> "/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
> "/var/ericsson/esm-data/postgresql-data" -o "-p 50432 -c autovacuum=off
> -c autovacuum_freeze_max_age=20  -c listen_addresses='' -c
> unix_socket_permissions=0700" start
> Failure, exiting
> 
> 
> 
> 
> With Best Regards
> Akshay
> 
> =-=-=
> Notice: The information contained in this e-mail
> message and/or attachments to it may contain
> confidential or privileged information. If you are
> not the intended recipient, any dissemination, use,
> review, distribution, printing or copying of the
> information contained in this e-mail message
> and/or attachments to it are strictly prohibited. If
> you have received this communication in error,
> please notify us by reply e-mail or telephone and
> immediately and permanently delete the message
> and any attachments. Thank you
> 

Re: How to fetch data from tables in PostgreSQL

[Greenhorn]

SELECT * FROM table_name LIMIT 10; should give 10 records from your data
table.

On Wed, 18 Apr 2018, 17:22 vaibhav zaveri,  wrote:

> Hi,
>
> How to fetch data from tables in PostgreSQL.
>
> Regards,
> Vaibhav Zaveri
>

Re: How to fetch data from tables in PostgreSQL

[Thomas Kellerer]

vaibhav zaveri schrieb am 18.04.2018 um 09:22:
> How to fetch data from tables in PostgreSQL.

That is done using the SELECT statement: 
https://www.postgresql.org/docs/current/static/sql-select.html

For an introduction on how to write queries, you might want to have a look at 
the Postgres tutorial:

https://www.postgresql.org/docs/current/static/tutorial-select.html

Thomas

Re: How to fetch data from tables in PostgreSQL

[Fabio Pardi]

Hi Vaibhav,

I think this might come of help:

https://www.postgresql.org/docs/current/static/tutorial-select.html




On 04/18/2018 09:22 AM, vaibhav zaveri wrote:
> Hi, 
> 
> How to fetch data from tables in PostgreSQL.
> 
> Regards, 
> Vaibhav Zaveri

pg_upgrade help

[Akshay Ballarpure]

Hi all,
I need help on pg_upgrade from 8.4 to 9.4 version. Appreciate urgent 
response.
Installed both version and stopped it. Do i need to run both version or 
only one 8.4 or 9.4 . Both should run on 50432 ?


-bash-4.2$ id
uid=26(postgres) gid=26(postgres) groups=26(postgres) 
context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

-bash-4.2$ export OLDCLUSTER=/var/ericsson/esm-data/postgresql-data -- 
8.4 data
-bash-4.2$ export NEWCLUSTER=/var/ericsson/esm-data/postgresql-data-9.4
 -- 9.4 data


-bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade 
--old-bindir=/usr/bin --new-bindir=/opt/rh/rh-postgresql94/root/usr/bin 
--old-datadir=$OLDCLUSTER --new-datadir=$NEWCLUSTER

connection to database failed: could not connect to server: No such file 
or directory
Is the server running locally and accepting
connections on Unix domain socket 
"/var/run/postgresql/.s.PGSQL.50432"?


could not connect to old postmaster started with the command:
"/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D 
"/var/ericsson/esm-data/postgresql-data" -o "-p 50432 -c autovacuum=off -c 
autovacuum_freeze_max_age=20  -c listen_addresses='' -c 
unix_socket_permissions=0700" start
Failure, exiting




With Best Regards
Akshay
=-=-=
Notice: The information contained in this e-mail
message and/or attachments to it may contain 
confidential or privileged information. If you are 
not the intended recipient, any dissemination, use, 
review, distribution, printing or copying of the 
information contained in this e-mail message 
and/or attachments to it are strictly prohibited. If 
you have received this communication in error, 
please notify us by reply e-mail or telephone and 
immediately and permanently delete the message 
and any attachments. Thank you

How to fetch data from tables in PostgreSQL

[vaibhav zaveri]

Hi,

How to fetch data from tables in PostgreSQL.

Regards,
Vaibhav Zaveri

Re: rehashing catalog cache id 14 for pg_opclass; 17 tups, 8 buckets

[Laurenz Albe]

Thiemo Kellner, NHC Barhufpflege wrote:
> > What do you have your log levels set to?
> 
> Thanks for pointing this out. I put client level to debug1. So, I am  
> just lucky not to have got flooded with Messages?

Sort of. This is a normal operation and should not worry you.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: rehashing catalog cache id 14 for pg_opclass; 17 tups, 8 buckets

[Thiemo Kellner, NHC Barhufpflege]

Zitat von Adrian Klaver :


What do you have your log levels set to?


Thanks for pointing this out. I put client level to debug1. So, I am  
just lucky not to have got flooded with Messages?



--
Öffentlicher PGP-Schlüssel:  
http://pgp.mit.edu/pks/lookup?op=get=0x8F70EFD2D972CBEF



This message was sent using IMP, the Internet Messaging Program.