pg_upgrade help

2018-04-18 Thread 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

RE: Installing PostgreSQL 9.5 in centos 6 using YUM

2018-04-18 Thread Ahmed, Nawaz 
Hi,

could you wget it and try installing instead ? or could you try an rpm that 
matches with your exact CentoOS version from here.

https://download.postgresql.org/pub/repos/yum/9.5/redhat/


Best Regards,

Nawaz Ahmed
Software Development Engineer

Fujitsu Australia Software Technology Pty Ltd
14 Rodborough Road, Frenchs Forest NSW 2086, Australia
T +61 2 9452 9027
[email protected]
fastware.com.au



From: Dinesh Chandra 12108 [mailto:[email protected]]
Sent: Tuesday, 17 April 2018 7:25 PM
To: [email protected]
Cc: [email protected]
Subject: Installing PostgreSQL 9.5 in centos 6 using YUM

Hi Team,

Could anyone help me to solve the below issue. I am installing PostgreSQL 9.5 
in centos 6 using YUM


[root@VM-02 PostgreSQL]# yum install 
https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-6-x86_64/pgdg-centos95-9.5-3.noarch.rpm

Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
* base: mirror.nbrc.ac.in
* extras: mirror.nbrc.ac.in
* updates: mirror.nbrc.ac.in
base/primary_db 
  |  41 kB 00:00
http://mirror.nbrc.ac.in/centos/6.9/os/x86_64/repodata/5d14ebd60604f4433dcc8a3a17cd3bbc7b80ec5dff74cbcc50dab6e711959265-primary.sqlite.bz2:
 [Errno -1] Metadata file does not match checksum
Trying other mirror.
base/primary_db 
  |  43 kB 00:00
http://centos.excellmedia.net/6.9/os/x86_64/repodata/5d14ebd60604f4433dcc8a3a17cd3bbc7b80ec5dff74cbcc50dab6e711959265-primary.sqlite.bz2:
 [Errno -1] Metadata file does not match checksum
Trying other mirror.
base/primary_db 
  |  45 kB 00:00
http://mirror.dhakacom.com/centos/6.9/os/x86_64/repodata/5d14ebd60604f4433dcc8a3a17cd3bbc7b80ec5dff74cbcc50dab6e711959265-primary.sqlite.bz2:
 [Errno -1] Metadata file does not match checksum
Trying other mirror.
base/primary_db 
  |  47 kB 00:00
http://mirror.xeonbd.com/centos/6.9/os/x86_64/repodata/5d14ebd60604f4433dcc8a3a17cd3bbc7b80ec5dff74cbcc50dab6e711959265-primary.sqlite.bz2:
 [Errno -1] Metadata file does not match checksum
Trying other mirror.
base/primary_db 
  |  49 kB 00:00
http://mirror.vbctv.in/centos/6.9/os/x86_64/repodata/5d14ebd60604f4433dcc8a3a17cd3bbc7b80ec5dff74cbcc50dab6e711959265-primary.sqlite.bz2:
 [Errno -1] Metadata file does not match checksum
Trying other mirror.
base/primary_db 
  |  51 kB 00:00
http://mirror.digistar.vn/centos/6.9/os/x86_64/repodata/5d14ebd60604f4433dcc8a3a17cd3bbc7b80ec5dff74cbcc50dab6e711959265-primary.sqlite.bz2:
 [Errno -1] Metadata file does not match checksum
Trying other mirror.
base/primary_db 
  |  53 kB 00:00
http://centos.myfahim.com/centos/6.9/os/x86_64/repodata/5d14ebd60604f4433dcc8a3a17cd3bbc7b80ec5dff74cbcc50dab6e711959265-primary.sqlite.bz2:
 [Errno -1] Metadata file does not match checksum
Trying other mirror.
base/primary_db 
  |  54 kB 00:00
http://ftp.iitm.ac.in/centos/6.9/os/x86_64/repodata/5d14ebd60604f4433dcc8a3a17cd3bbc7b80ec5dff74cbcc50dab6e711959265-primary.sqlite.bz2:
 [Errno -1] Metadata file does not match checksum
Trying other mirror.
base/primary_db 
  |  56 kB 00:00
http://centos.mirror.net.in/centos/6.9/os/x86_64/repodata/5d14ebd60604f4433dcc8a3a17cd3bbc7b80ec5dff74cbcc50dab6e711959265-primary.sqlite.bz2:
 [Errno -1] Metadata file does not match checksum
Trying other mirror.
base/primary_db 
  |  58 kB 00:00
http://del-mirrors.extreme-ix.org/centos/6.9/os/x86_64/repodata/5d14ebd60604f4433dcc8a3a17cd3bbc7b80ec5dff74cbcc50dab6e711959265-primary.sqlite.bz2:
 [Errno -1] Metadata file does not match checksum
Trying other mirror.
Error: failure: 
repodata/5d14ebd60604f4433dcc8a3a17cd3bbc7b80ec5dff74cbcc50dab6e711959265-primary.sqlite.bz2
 from base: [Errno 256] No more mirrors to try.

Regards,
Dinesh Chandra

RE: Data migration from postgres 8.4 to 9.4

2018-04-18 Thread Ahmed, Nawaz 
Hi,

pg_upgrade does not need installation, it comes with a PostgreSQL installation.

You will find it in  the bin directory of your 9.4 PostgreSQL installation.


Best Regards,

Nawaz Ahmed
Software Development Engineer

Fujitsu Australia Software Technology Pty Ltd
14 Rodborough Road, Frenchs Forest NSW 2086, Australia
T +61 2 9452 9027
[email protected]
fastware.com.au



From: Akshay Ballarpure [mailto:[email protected]]
Sent: Tuesday, 17 April 2018 10:24 PM
To: Albin, Lloyd P ; [email protected]; 
[email protected]
Subject: RE: Data migration from postgres 8.4 to 9.4

Thank you for detailed info. much appreciated. May i know how to install 
pg_upgrade ?


With Best Regards
Akshay




From:"Albin, Lloyd P" mailto:[email protected]>>
To:Akshay Ballarpure 
mailto:[email protected]>>
Date:04/16/2018 08:38 PM
Subject:RE: Data migration from postgres 8.4 to 9.4




Akshay ,

There are several Official ways to upgrade PostgreSQL.

1) Use pg_upgrade (Faster) Postgres 8.4 to Postgres 9.4. Use the Postgres 9.4 
version of pg_upgrade.
https://www.postgresql.org/docs/9.4/static/pgupgrade.html

2) Dump and Restore your database into a new server (Slower) Postgres 8.4 to 
Postgres 9.4 Use pg_dump with pg_restore or pg_dumpall with psql from Postgres 
9.4 against your Postgres 8.4 Server. You need to use this method if you wish 
to change your initdb settings, such as the default encoding, turn on 
checksums, etc.
https://www.postgresql.org/docs/9.4/static/app-pgdump.html
https://www.postgresql.org/docs/9.4/static/app-pgrestore.html
https://www.postgresql.org/docs/9.4/static/app-pg-dumpall.html

3) Swap out the binaries. This can only be done using the same Postgres version 
(8.4.x or 9.4.x or 10.x) This means that you can upgrade from 9.4.9 to 9.4.12 
by just swapping out the binaries.

4) Unofficially you can use things like slony, etc to do a live migration 
without downtime.

Lloyd



From: Akshay Ballarpure [[email protected]]
Sent: Monday, April 16, 2018 12:03 AM
Subject: Data migration from postgres 8.4 to 9.4

Hello,
I need help in using postgresql 8.4 data in postgres 9.4 version. Do I need to 
run any tool to achieve the same?

Steps i followed is ran postgresql 8.4 and 9.4, copied data from 8.4 instance 
to 9.4 and try to start postgresql 9.4 but no luck, getting below error.

[root@ms-esmon esm-data]# su - postgres -c 
"/opt/rh/rh-postgresql94/root/usr/bin/postgres -D 
/var/ericsson/esm-data/postgresql-data/ 2>&1 &"
[root@ms-esmon esm-data]# LOG:  skipping missing configuration file 
"/var/ericsson/esm-data/postgresql-data/postgresql.auto.conf"
2018-04-16 06:52:01.546 GMT  FATAL:  database files are incompatible with server
2018-04-16 06:52:01.546 GMT  DETAIL:  The data directory was initialized by 
PostgreSQL version 8.4, which is not compatible with this version 9.4.9.


With Best Regards
Akshay
Ericsson OSS MON
Tata Consultancy Services
Mailto: [email protected]
Website: 
http://www.tcs.com

Experience certainty.IT Services
  Business Solutions
  Consulting

=-=-=
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
Disclaimer

The information in this e-mail is confidential and may contain content that is 
subject to copyright and/or is commercial-in-confidence and is intended only 
for the use of the above named addressee. If you are not the intended 
recipient, you are hereby notified that dissemination, copying or use of the 
information is strictly prohibited. If you have received this e-mail in error, 
please telephone Fujitsu Australia Software Technology Pty Ltd on + 61 2 9452 
9000 or by reply e-mail to the sender and delete the document and all copies 
thereof.


Whereas Fujitsu Australia Software Technology Pty Ltd would not knowingly 
transmit a virus within an email communication, it is the receiver’s 
responsibility to scan all communication and any files attached for computer 
viruses and other defects. Fuji

Re: pg_upgrade help

2018-04-18 Thread 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

SeqScan vs. IndexScan

2018-04-18 Thread Vitaliy Garnashevich 

Hi,

I'm running the same query with "set enable_seqscan = on;" and "set 
enable_seqscan = off;":


->  Nested Loop Left Join  (cost=0.00..89642.86 rows=1 width=30) (actual 
time=1.612..6924.232 rows=3289 loops=1)

  Join Filter: (sys_user.user_id = j_6634.id)
  Rows Removed by Join Filter: 14330174
  ->  Seq Scan on sys_user  (cost=0.00..89449.85 rows=1 width=16) 
(actual time=0.117..39.802 rows=3289 loops=1)

    Filter: ...
  ->  Seq Scan on cmn_user j_6634  (cost=0.00..138.56 rows=4356 
width=22) (actual time=0.001..0.973 rows=4358 loops=3289)


(Full plan: https://explain.depesz.com/s/plAO)

->  Nested Loop Left Join  (cost=0.56..89643.52 rows=1 width=30) (actual 
time=0.589..39.674 rows=3288 loops=1)
  ->  Index Scan using sys_user_pkey on sys_user 
(cost=0.28..89635.21 rows=1 width=16) (actual time=0.542..29.435 
rows=3288 loops=1)

    Filter: ...
  ->  Index Scan using cmn_user_pkey on cmn_user j_6634 
(cost=0.28..8.30 rows=1 width=22) (actual time=0.002..0.002 rows=1 
loops=3288)

    Index Cond: (sys_user.user_id = id)

(Full plan: https://explain.depesz.com/s/4QXy)

Why optimizer is choosing SeqScan (on cmn_user) in the first query, 
instead of an IndexScan, despite of SeqScan being more costly?


Regards,
Vitaliy

Re: SeqScan vs. IndexScan

2018-04-18 Thread Tom Lane 
Vitaliy Garnashevich  writes:
> I'm running the same query with "set enable_seqscan = on;" and "set 
> enable_seqscan = off;":
> ...
> Why optimizer is choosing SeqScan (on cmn_user) in the first query, 
> instead of an IndexScan, despite of SeqScan being more costly?

Because it cares about the total plan cost, not the cost of any one
sub-node.  In this case, the total costs at the join level are fuzzily
the same, but the indexscan-based join has worse estimated startup cost,
so it prefers the first choice.

The real problem here is the discrepancy between estimate and reality
for the number of rows out of the sys_user scan; because of that, you're
going to get garbage choices at the join level no matter what :-(.
You should look into what's causing that misestimate and whether you
can reduce the error, perhaps by providing better stats or reformulating
the filter conditions in a way the optimizer understands better.

regards, tom lane