Re: [GENERAL] pg_basebackup ----xlog-method=stream

[Ian Harding]

> On Apr 21, 2017, at 3:29 PM, Michael Paquier  
> wrote:
> 
>> On Sat, Apr 22, 2017 at 3:02 AM, Ian Harding  wrote:
>> Am I misunderstanding how this works?  I have WAL archiving set up, so the
>> files are available, but I wanted them included in the backup.
> 
> Please note that if you have a WAL archive available, you may not even
> need to have --xlog-method=stream, which is useful to have fully
> safe-contailed backups. For the error regarding the missing WAL
> segment, it could happen if Postgres completes two checkpoints when
> taking the backup. One thing that you could use is a replication slot
> that gives the guarantee that segments are retained. Those have been
> integrated in pg_basebackup with 9.6.

Yeah. I really want to use replication slots but put the server into production 
without setting the appropriate config settings. I need to schedule some 
downtime for a restart. 

> -- 
> Michael


-- 
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] Recover PostgreSQL database folder data

[Cat]

On Fri, Apr 21, 2017 at 08:20:38PM -0300, Edson Lidorio wrote:
> Ls -la /var/lib/pgsql/9.6/data
> 
> drwx--. 20 postgres postgres  4096 Abr 21 17:52 .
> drwx--.  4 root root51 Abr 21 06:33 ..

Ensure that the user 'postgres' has permissions to get to
this dir from / up. This may either mean changing permissions
on some directories or changing ownership.

More than likely / /var /lib are a permissions thing (likely
need to be u+rwx,g+rx,o+rx) and /var/lib/pgsql/ and up is an
ownership thing (postgres:postgres) but this is not guaranteed
so take care.

-- 
  "A search of his car uncovered pornography, a homemade sex aid, women's 
  stockings and a Jack Russell terrier."
- 
http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-118083480


-- 
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] Recover PostgreSQL database folder data

[Adrian Klaver]

On 04/21/2017 04:20 PM, Edson Lidorio wrote:



On 21-04-2017 18:13, Edson Lidorio wrote:



On 21-04-2017 18:02, Adrian Klaver wrote:

Chown -R postgres: postgres /var/lib/pgsql/9.6/data

After the command, I have the errors:

Apr 21 18:11:41 localhost postgresql96-check-db-dir:
"/var/lib/pgsql/9.6/data/" is missing or empty.
Apr 21 18:11:41 localhost postgresql96-check-db-dir: Use
"/usr/pgsql-9.6/bin/postgresql96-setup initdb" to initialize the
database cluster.
Apr 21 18:11:41 localhost postgresql96-check-db-dir: See
/usr/share/doc/postgresql96/README.rpm-dist for more information.
Apr 21 18:11:41 localhost systemd: postgresql-9.6.service: control
process exited, code=exited status=1
Apr 21 18:11:41 localhost systemd: Failed to start PostgreSQL 9.6
database server.
Apr 21 18:11:41 localhost systemd: Unit postgresql-9.6.service entered
failed state.
Apr 21 18:11:41 localhost systemd: postgresql-9.6.service failed.


Follows all the results:

What does the directory listing for /var/lib/pgsql/9.6/data  show?
[root@localhost data]# pwd
/var/lib/pgsql/9.6/data


Ls -la /var/lib/pgsql/9.6/data

drwx--. 20 postgres postgres  4096 Abr 21 17:52 .
drwx--.  4 root root51 Abr 21 06:33 ..
-rw---.  1 postgres postgres 0 Abr 21 06:33 ArquivoASerCriado.dmp
drwx--. 44 postgres postgres  4096 Abr 21 06:32 base
drwx--.  2 postgres postgres  4096 Abr 21 06:32 global
drwx--.  2 postgres postgres18 Abr 21 06:32 pg_clog
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_commit_ts
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_dynshmem
-rw---.  1 postgres postgres  4345 Abr 21 06:33 pg_hba.conf
-rw---.  1 postgres postgres  1636 Abr 21 06:33 pg_ident.conf
drwx--.  2 postgres postgres   188 Abr 21 06:32 pg_log
drwx--.  4 postgres postgres39 Abr 21 06:32 pg_logical
drwx--.  4 postgres postgres36 Abr 21 06:32 pg_multixact
drwx--.  2 postgres postgres18 Abr 21 06:32 pg_notify
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_replslot
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_serial
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_snapshots
drwx--.  2 postgres postgres  4096 Abr 21 06:32 pg_stat
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_stat_tmp
drwx--.  2 postgres postgres18 Abr 21 06:32 pg_subtrans
drwx--.  2 postgres postgres84 Abr 21 06:32 pg_tblspc
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_twophase
-rw---.  1 postgres postgres 4 Abr 21 06:33 PG_VERSION
drwx--.  3 postgres postgres  4096 Abr 21 06:33 pg_xlog
-rw---.  1 postgres postgres88 Abr 21 06:33 postgresql.auto.conf
-rw---.  1 postgres postgres 22289 Abr 21 06:33 postgresql.conf
-rw---.  1 postgres postgres60 Abr 21 06:33 postmaster.opts

Ensure you actually have a folder named "/var/lib/pgsql/9.6/data" with your
database in it (not, possibly, a folder named "date" per your original
message).

[root@localhost 9.6]# du -hs *
0backups
4,2Gdata
4,0Kinitdb.log

chown -R postgres:postgres /var/lib/pgsql/9.6/data

systemctl start postgresql-9.6.service
Job for postgresql-9.6.service failed because the control process exited
with error code. See "systemctl status postgresql-9.6.service" and
"journalctl -xe" for details.

restorecon -R /var/lib/pgsql/9.6/data

Apr 21 19:11:58 localhost systemd: Starting PostgreSQL 9.6 database
server...
Apr 21 19:11:58 localhost postgresql96-check-db-dir:
"/var/lib/pgsql/9.6/data/" is missing or empty.
Apr 21 19:11:58 localhost postgresql96-check-db-dir: Use
"/usr/pgsql-9.6/bin/postgresql96-setup initdb" to initialize the
database cluster.
Apr 21 19:11:58 localhost postgresql96-check-db-dir: See
/usr/share/doc/postgresql96/README.rpm-dist for more information.
Apr 21 19:11:58 localhost systemd: postgresql-9.6.service: control
process exited, code=exited status=1
Apr 21 19:11:58 localhost systemd: Failed to start PostgreSQL 9.6
database server.
Apr 21 19:11:58 localhost systemd: Unit postgresql-9.6.service entered
failed state.
Apr 21 19:11:58 localhost systemd: postgresql-9.6.service failed.


If it still won't start after that, please do an "ls -alRZ
/var/lib/pgsql/9.6", pastebin the output, and send the link.

Download Link: http://myaccount.dropsend.com/file/2714afb84f8ecadd


Found postgresql96-check-db-dir in the RPM, it looks straight forward 
and should not be failing from what I see.








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

Re: [GENERAL] Recover PostgreSQL database folder data

[Edson Lidorio]

On 21-04-2017 20:55, Adrian Klaver wrote:

On 04/21/2017 04:20 PM, Edson Lidorio wrote:



On 21-04-2017 18:13, Edson Lidorio wrote:



What does the directory listing for /var/lib/pgsql/9.6/data  show?
[root@localhost data]# pwd
/var/lib/pgsql/9.6/data


Ls -la /var/lib/pgsql/9.6/data

drwx--. 20 postgres postgres  4096 Abr 21 17:52 .
drwx--.  4 root root51 Abr 21 06:33 ..
-rw---.  1 postgres postgres 0 Abr 21 06:33 
ArquivoASerCriado.dmp

drwx--. 44 postgres postgres  4096 Abr 21 06:32 base
drwx--.  2 postgres postgres  4096 Abr 21 06:32 global
drwx--.  2 postgres postgres18 Abr 21 06:32 pg_clog
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_commit_ts
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_dynshmem
-rw---.  1 postgres postgres  4345 Abr 21 06:33 pg_hba.conf
-rw---.  1 postgres postgres  1636 Abr 21 06:33 pg_ident.conf
drwx--.  2 postgres postgres   188 Abr 21 06:32 pg_log
drwx--.  4 postgres postgres39 Abr 21 06:32 pg_logical
drwx--.  4 postgres postgres36 Abr 21 06:32 pg_multixact
drwx--.  2 postgres postgres18 Abr 21 06:32 pg_notify
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_replslot
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_serial
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_snapshots
drwx--.  2 postgres postgres  4096 Abr 21 06:32 pg_stat
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_stat_tmp
drwx--.  2 postgres postgres18 Abr 21 06:32 pg_subtrans
drwx--.  2 postgres postgres84 Abr 21 06:32 pg_tblspc
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_twophase
-rw---.  1 postgres postgres 4 Abr 21 06:33 PG_VERSION
drwx--.  3 postgres postgres  4096 Abr 21 06:33 pg_xlog
-rw---.  1 postgres postgres88 Abr 21 06:33 postgresql.auto.conf
-rw---.  1 postgres postgres 22289 Abr 21 06:33 postgresql.conf
-rw---.  1 postgres postgres60 Abr 21 06:33 postmaster.opts

Ensure you actually have a folder named "/var/lib/pgsql/9.6/data" 
with your

database in it (not, possibly, a folder named "date" per your original
message).

[root@localhost 9.6]# du -hs *
0backups
4,2Gdata
4,0Kinitdb.log

chown -R postgres:postgres /var/lib/pgsql/9.6/data

systemctl start postgresql-9.6.service
Job for postgresql-9.6.service failed because the control process exited
with error code. See "systemctl status postgresql-9.6.service" and
"journalctl -xe" for details.

restorecon -R /var/lib/pgsql/9.6/data

Apr 21 19:11:58 localhost systemd: Starting PostgreSQL 9.6 database
server...
Apr 21 19:11:58 localhost postgresql96-check-db-dir:
"/var/lib/pgsql/9.6/data/" is missing or empty.
Apr 21 19:11:58 localhost postgresql96-check-db-dir: Use
"/usr/pgsql-9.6/bin/postgresql96-setup initdb" to initialize the
database cluster.
Apr 21 19:11:58 localhost postgresql96-check-db-dir: See
/usr/share/doc/postgresql96/README.rpm-dist for more information.
Apr 21 19:11:58 localhost systemd: postgresql-9.6.service: control
process exited, code=exited status=1
Apr 21 19:11:58 localhost systemd: Failed to start PostgreSQL 9.6
database server.
Apr 21 19:11:58 localhost systemd: Unit postgresql-9.6.service entered
failed state.
Apr 21 19:11:58 localhost systemd: postgresql-9.6.service failed.


If it still won't start after that, please do an "ls -alRZ
/var/lib/pgsql/9.6", pastebin the output, and send the link.

Download Link: http://myaccount.dropsend.com/file/2714afb84f8ecadd


Not sure it applies here,  but I see that in the above file you have 
tablespace links.


Are those tablespaces actually there?

Can you find postgresql96-check-db-dir and see what it is looking for?








The tablespace exists I have restored in the same directory that was
/var/tablespace_pgsql
 chown -R postgres:postgres /var/tablespace_pgsql/
chmod 700 /var/tablespace_pgsql/



--
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] Recover PostgreSQL database folder data

[Adrian Klaver]

On 04/21/2017 04:20 PM, Edson Lidorio wrote:



On 21-04-2017 18:13, Edson Lidorio wrote:



What does the directory listing for /var/lib/pgsql/9.6/data  show?
[root@localhost data]# pwd
/var/lib/pgsql/9.6/data


Ls -la /var/lib/pgsql/9.6/data

drwx--. 20 postgres postgres  4096 Abr 21 17:52 .
drwx--.  4 root root51 Abr 21 06:33 ..
-rw---.  1 postgres postgres 0 Abr 21 06:33 ArquivoASerCriado.dmp
drwx--. 44 postgres postgres  4096 Abr 21 06:32 base
drwx--.  2 postgres postgres  4096 Abr 21 06:32 global
drwx--.  2 postgres postgres18 Abr 21 06:32 pg_clog
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_commit_ts
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_dynshmem
-rw---.  1 postgres postgres  4345 Abr 21 06:33 pg_hba.conf
-rw---.  1 postgres postgres  1636 Abr 21 06:33 pg_ident.conf
drwx--.  2 postgres postgres   188 Abr 21 06:32 pg_log
drwx--.  4 postgres postgres39 Abr 21 06:32 pg_logical
drwx--.  4 postgres postgres36 Abr 21 06:32 pg_multixact
drwx--.  2 postgres postgres18 Abr 21 06:32 pg_notify
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_replslot
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_serial
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_snapshots
drwx--.  2 postgres postgres  4096 Abr 21 06:32 pg_stat
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_stat_tmp
drwx--.  2 postgres postgres18 Abr 21 06:32 pg_subtrans
drwx--.  2 postgres postgres84 Abr 21 06:32 pg_tblspc
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_twophase
-rw---.  1 postgres postgres 4 Abr 21 06:33 PG_VERSION
drwx--.  3 postgres postgres  4096 Abr 21 06:33 pg_xlog
-rw---.  1 postgres postgres88 Abr 21 06:33 postgresql.auto.conf
-rw---.  1 postgres postgres 22289 Abr 21 06:33 postgresql.conf
-rw---.  1 postgres postgres60 Abr 21 06:33 postmaster.opts

Ensure you actually have a folder named "/var/lib/pgsql/9.6/data" with your
database in it (not, possibly, a folder named "date" per your original
message).

[root@localhost 9.6]# du -hs *
0backups
4,2Gdata
4,0Kinitdb.log

chown -R postgres:postgres /var/lib/pgsql/9.6/data

systemctl start postgresql-9.6.service
Job for postgresql-9.6.service failed because the control process exited
with error code. See "systemctl status postgresql-9.6.service" and
"journalctl -xe" for details.

restorecon -R /var/lib/pgsql/9.6/data

Apr 21 19:11:58 localhost systemd: Starting PostgreSQL 9.6 database
server...
Apr 21 19:11:58 localhost postgresql96-check-db-dir:
"/var/lib/pgsql/9.6/data/" is missing or empty.
Apr 21 19:11:58 localhost postgresql96-check-db-dir: Use
"/usr/pgsql-9.6/bin/postgresql96-setup initdb" to initialize the
database cluster.
Apr 21 19:11:58 localhost postgresql96-check-db-dir: See
/usr/share/doc/postgresql96/README.rpm-dist for more information.
Apr 21 19:11:58 localhost systemd: postgresql-9.6.service: control
process exited, code=exited status=1
Apr 21 19:11:58 localhost systemd: Failed to start PostgreSQL 9.6
database server.
Apr 21 19:11:58 localhost systemd: Unit postgresql-9.6.service entered
failed state.
Apr 21 19:11:58 localhost systemd: postgresql-9.6.service failed.


If it still won't start after that, please do an "ls -alRZ
/var/lib/pgsql/9.6", pastebin the output, and send the link.

Download Link: http://myaccount.dropsend.com/file/2714afb84f8ecadd


Not sure it applies here,  but I see that in the above file you have 
tablespace links.


Are those tablespaces actually there?

Can you find postgresql96-check-db-dir and see what it is looking for?







--
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] Strange Issue between PSQL 9.3 and Label Zebra Printer?

[Periko Support]

Hi guys.

I would to explain my current strange issue with our label printer
Zebra ZT230 USB.

Windows 8.1 x64, psqlodbc 9.5x86/x64 drivers tested.

The issue is that every time we print labels that read data from the
DB, the print start printing
with pause on every label.

Is like, select data, send result to printer, print the label, stop,
request new record start over.

PSQL is running under Linux Ubuntu 14.x PSQL 9.3.x

We have other printer from DataMAX which we don't have any issue and
the connection is by LAN.

Wondering is some one here have in the past issues like this one?

Thanks for your time!!!


-- 
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] Recover PostgreSQL database folder data

[David G. Johnston]

On Friday, April 21, 2017, Edson Lidorio  wrote:
>
> 4- I gave permission in the folder date:
>  sudo chown postgres: postgres /var/lib/pgsql/9.6/data;
>  sudo chmod 700 /var/lib/pgsql/9.6/data
>

You might want to check and see what permissions the original package
installed directory had and emulate those instead of blindly dropping a 700
on it.

David J.

Re: [GENERAL] Recover PostgreSQL database folder data

[Edson Lidorio]

On 21-04-2017 18:13, Edson Lidorio wrote:



On 21-04-2017 18:02, Adrian Klaver wrote:
Chown -R postgres: postgres /var/lib/pgsql/9.6/data 

After the command, I have the errors:

Apr 21 18:11:41 localhost postgresql96-check-db-dir: 
"/var/lib/pgsql/9.6/data/" is missing or empty.
Apr 21 18:11:41 localhost postgresql96-check-db-dir: Use 
"/usr/pgsql-9.6/bin/postgresql96-setup initdb" to initialize the 
database cluster.
Apr 21 18:11:41 localhost postgresql96-check-db-dir: See 
/usr/share/doc/postgresql96/README.rpm-dist for more information.
Apr 21 18:11:41 localhost systemd: postgresql-9.6.service: control 
process exited, code=exited status=1
Apr 21 18:11:41 localhost systemd: Failed to start PostgreSQL 9.6 
database server.
Apr 21 18:11:41 localhost systemd: Unit postgresql-9.6.service entered 
failed state.

Apr 21 18:11:41 localhost systemd: postgresql-9.6.service failed.


Follows all the results:

What does the directory listing for /var/lib/pgsql/9.6/data  show?
[root@localhost data]# pwd
/var/lib/pgsql/9.6/data


Ls -la /var/lib/pgsql/9.6/data

drwx--. 20 postgres postgres  4096 Abr 21 17:52 .
drwx--.  4 root root51 Abr 21 06:33 ..
-rw---.  1 postgres postgres 0 Abr 21 06:33 ArquivoASerCriado.dmp
drwx--. 44 postgres postgres  4096 Abr 21 06:32 base
drwx--.  2 postgres postgres  4096 Abr 21 06:32 global
drwx--.  2 postgres postgres18 Abr 21 06:32 pg_clog
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_commit_ts
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_dynshmem
-rw---.  1 postgres postgres  4345 Abr 21 06:33 pg_hba.conf
-rw---.  1 postgres postgres  1636 Abr 21 06:33 pg_ident.conf
drwx--.  2 postgres postgres   188 Abr 21 06:32 pg_log
drwx--.  4 postgres postgres39 Abr 21 06:32 pg_logical
drwx--.  4 postgres postgres36 Abr 21 06:32 pg_multixact
drwx--.  2 postgres postgres18 Abr 21 06:32 pg_notify
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_replslot
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_serial
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_snapshots
drwx--.  2 postgres postgres  4096 Abr 21 06:32 pg_stat
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_stat_tmp
drwx--.  2 postgres postgres18 Abr 21 06:32 pg_subtrans
drwx--.  2 postgres postgres84 Abr 21 06:32 pg_tblspc
drwx--.  2 postgres postgres 6 Abr 21 06:32 pg_twophase
-rw---.  1 postgres postgres 4 Abr 21 06:33 PG_VERSION
drwx--.  3 postgres postgres  4096 Abr 21 06:33 pg_xlog
-rw---.  1 postgres postgres88 Abr 21 06:33 postgresql.auto.conf
-rw---.  1 postgres postgres 22289 Abr 21 06:33 postgresql.conf
-rw---.  1 postgres postgres60 Abr 21 06:33 postmaster.opts

Ensure you actually have a folder named "/var/lib/pgsql/9.6/data" with your
database in it (not, possibly, a folder named "date" per your original
message).

[root@localhost 9.6]# du -hs *
0backups
4,2Gdata
4,0Kinitdb.log

chown -R postgres:postgres /var/lib/pgsql/9.6/data

systemctl start postgresql-9.6.service
Job for postgresql-9.6.service failed because the control process exited 
with error code. See "systemctl status postgresql-9.6.service" and 
"journalctl -xe" for details.


restorecon -R /var/lib/pgsql/9.6/data

Apr 21 19:11:58 localhost systemd: Starting PostgreSQL 9.6 database 
server...
Apr 21 19:11:58 localhost postgresql96-check-db-dir: 
"/var/lib/pgsql/9.6/data/" is missing or empty.
Apr 21 19:11:58 localhost postgresql96-check-db-dir: Use 
"/usr/pgsql-9.6/bin/postgresql96-setup initdb" to initialize the 
database cluster.
Apr 21 19:11:58 localhost postgresql96-check-db-dir: See 
/usr/share/doc/postgresql96/README.rpm-dist for more information.
Apr 21 19:11:58 localhost systemd: postgresql-9.6.service: control 
process exited, code=exited status=1
Apr 21 19:11:58 localhost systemd: Failed to start PostgreSQL 9.6 
database server.
Apr 21 19:11:58 localhost systemd: Unit postgresql-9.6.service entered 
failed state.

Apr 21 19:11:58 localhost systemd: postgresql-9.6.service failed.


If it still won't start after that, please do an "ls -alRZ
/var/lib/pgsql/9.6", pastebin the output, and send the link.

Download Link: http://myaccount.dropsend.com/file/2714afb84f8ecadd


--
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] pg_basebackup ----xlog-method=stream

[Michael Paquier]

On Sat, Apr 22, 2017 at 3:02 AM, Ian Harding  wrote:
> Am I misunderstanding how this works?  I have WAL archiving set up, so the
> files are available, but I wanted them included in the backup.

Please note that if you have a WAL archive available, you may not even
need to have --xlog-method=stream, which is useful to have fully
safe-contailed backups. For the error regarding the missing WAL
segment, it could happen if Postgres completes two checkpoints when
taking the backup. One thing that you could use is a replication slot
that gives the guarantee that segments are retained. Those have been
integrated in pg_basebackup with 9.6.
-- 
Michael


-- 
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] Recover PostgreSQL database folder data

[Alan Hodgson]

> On Fri, Apr 21, 2017 at 12:40 PM, Edson Lidorio 
> 
> wrote:
> > Hi,
> > There was a disaster in my development note. I was able to recover the
> > data folder. PostgreSQL 9.6.2, was installed in Centos 7.
> > 
> > Here are the procedures I'm trying to initialize Postgresql for me to do a
> > backup.
> > 
> > 1- I installed PostgreSQL 9.6.2 on a VM with Centos 7.
> > 2- I stopped the PostgreSQL service: sudo systemctl stop postgresql-9.6
> > 3- I renamed the /var/lib/pgsql/9.6/data folder to date data_old and
> > copied the old date folder
> > 
> > 4- I gave permission in the folder date:
> >  sudo chown postgres: postgres /var/lib/pgsql/9.6/data;

Ensure you actually have a folder named "/var/lib/pgsql/9.6/data" with your 
database in it (not, possibly, a folder named "date" per your original 
message).

as root:

chown -R postgres:postgres /var/lib/pgsql/9.6/data
restorecon -R /var/lib/pgsql/9.6/data

If it still won't start after that, please do an "ls -alRZ 
/var/lib/pgsql/9.6", pastebin the output, and send the link.


-- 
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] Recover PostgreSQL database folder data

[John R Pierce]

On 4/21/2017 2:13 PM, Edson Lidorio wrote:
Apr 21 18:11:41 localhost postgresql96-check-db-dir: 
"/var/lib/pgsql/9.6/data/" is missing or empty. 


whats there?

ls -la /var/lib/pgsql/9.6/data


--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Recover PostgreSQL database folder data

[Adrian Klaver]

On 04/21/2017 02:13 PM, Edson Lidorio wrote:



On 21-04-2017 18:02, Adrian Klaver wrote:

Chown -R postgres: postgres /var/lib/pgsql/9.6/data

After the command, I have the errors:


What does the directory listing for /var/lib/pgsql/9.6/data  show?


Apr 21 18:11:41 localhost postgresql96-check-db-dir:
"/var/lib/pgsql/9.6/data/" is missing or empty.
Apr 21 18:11:41 localhost postgresql96-check-db-dir: Use
"/usr/pgsql-9.6/bin/postgresql96-setup initdb" to initialize the
database cluster.
Apr 21 18:11:41 localhost postgresql96-check-db-dir: See
/usr/share/doc/postgresql96/README.rpm-dist for more information.
Apr 21 18:11:41 localhost systemd: postgresql-9.6.service: control
process exited, code=exited status=1
Apr 21 18:11:41 localhost systemd: Failed to start PostgreSQL 9.6
database server.
Apr 21 18:11:41 localhost systemd: Unit postgresql-9.6.service entered
failed state.
Apr 21 18:11:41 localhost systemd: postgresql-9.6.service failed.






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

Re: [GENERAL] Recover PostgreSQL database folder data

[Edson Lidorio]

On 21-04-2017 18:02, Adrian Klaver wrote:
Chown -R postgres: postgres /var/lib/pgsql/9.6/data 

After the command, I have the errors:

Apr 21 18:11:41 localhost postgresql96-check-db-dir: 
"/var/lib/pgsql/9.6/data/" is missing or empty.
Apr 21 18:11:41 localhost postgresql96-check-db-dir: Use 
"/usr/pgsql-9.6/bin/postgresql96-setup initdb" to initialize the 
database cluster.
Apr 21 18:11:41 localhost postgresql96-check-db-dir: See 
/usr/share/doc/postgresql96/README.rpm-dist for more information.
Apr 21 18:11:41 localhost systemd: postgresql-9.6.service: control 
process exited, code=exited status=1
Apr 21 18:11:41 localhost systemd: Failed to start PostgreSQL 9.6 
database server.
Apr 21 18:11:41 localhost systemd: Unit postgresql-9.6.service entered 
failed state.

Apr 21 18:11:41 localhost systemd: postgresql-9.6.service failed.



--
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] Recover PostgreSQL database folder data

[Adrian Klaver]

On 04/21/2017 01:53 PM, Edson Lidorio wrote:



On 21-04-2017 17:43, Adrian Klaver wrote:

On 04/21/2017 01:36 PM, Edson Lidorio wrote:



On 21-04-2017 17:27, Scott Mead wrote:

chown *-R* postgres:postgres /var/lib/pgsql/9.6/data


Chown -R postgres: postgres /var/lib/pgsql/9.6/data
  Sudo systemctl start postgresql-9.6


Is this on the first VM you showed previously or the new one you said
you where going to create?



Erros:

Unit postgresql-9.6.service entered failed state.
Abr 21 07:50:11 localhost.localdomain systemd[1]: postgresql-9.6.service
failed.
Abr 21 07:50:11 localhost.localdomain polkitd[693]: Unregistered
Authentication Agent for unix-process:30276:3843896 (system bus name
:1.486, object p
lines 2341-2377/2377 (END)


/var/lib/pgsql/9.6/data/" is missing or empty.


Is it empty, because before it was not?



Apr 21 07:50:11 localhost postgresql96-check-db-dir: Use
"/usr/pgsql-9.6/bin/postgresql96-setup initdb" to initialize the
database cluster.
Apr 21 07:50:11 localhost postgresql96-check-db-dir: See
/usr/share/doc/postgresql96/README.rpm-dist for more information.
Apr 21 07:50:11 localhost systemd: postgresql-9.6.service: control
process exited, code=exited status=1
Apr 21 07:50:11 localhost systemd: Failed to start PostgreSQL 9.6
database server.
Apr 21 07:50:11 localhost systemd: Unit postgresql-9.6.service entered
failed state.
Apr 21 07:50:11 localhost systemd: postgresql-9.6.service failed.






In the new


So why not try:

chown -R postgres:postgres /var/lib/pgsql/9.6/data

on the previous system. Not sure if is was a transcribing error, but in 
your previous post you had:


Chown -R postgres: postgres /var/lib/pgsql/9.6/data

which would not work.



Is it empty, because before it was not?
It's strange the date folder, old is there with the data!


Just to be clear we are talking about the data/ directory.

Could we see the listings for data/ data_old?






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

Re: [GENERAL] Recover PostgreSQL database folder data

[Edson Lidorio]

On 21-04-2017 17:43, Adrian Klaver wrote:

On 04/21/2017 01:36 PM, Edson Lidorio wrote:



On 21-04-2017 17:27, Scott Mead wrote:

chown *-R* postgres:postgres /var/lib/pgsql/9.6/data


Chown -R postgres: postgres /var/lib/pgsql/9.6/data
  Sudo systemctl start postgresql-9.6


Is this on the first VM you showed previously or the new one you said 
you where going to create?




Erros:

Unit postgresql-9.6.service entered failed state.
Abr 21 07:50:11 localhost.localdomain systemd[1]: postgresql-9.6.service
failed.
Abr 21 07:50:11 localhost.localdomain polkitd[693]: Unregistered
Authentication Agent for unix-process:30276:3843896 (system bus name
:1.486, object p
lines 2341-2377/2377 (END)


/var/lib/pgsql/9.6/data/" is missing or empty.


Is it empty, because before it was not?



Apr 21 07:50:11 localhost postgresql96-check-db-dir: Use
"/usr/pgsql-9.6/bin/postgresql96-setup initdb" to initialize the
database cluster.
Apr 21 07:50:11 localhost postgresql96-check-db-dir: See
/usr/share/doc/postgresql96/README.rpm-dist for more information.
Apr 21 07:50:11 localhost systemd: postgresql-9.6.service: control
process exited, code=exited status=1
Apr 21 07:50:11 localhost systemd: Failed to start PostgreSQL 9.6
database server.
Apr 21 07:50:11 localhost systemd: Unit postgresql-9.6.service entered
failed state.
Apr 21 07:50:11 localhost systemd: postgresql-9.6.service failed.






In the new

Is it empty, because before it was not?
It's strange the date folder, old is there with the data!


--
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] Recover PostgreSQL database folder data

[Adrian Klaver]

On 04/21/2017 01:36 PM, Edson Lidorio wrote:



On 21-04-2017 17:27, Scott Mead wrote:

chown *-R* postgres:postgres /var/lib/pgsql/9.6/data


Chown -R postgres: postgres /var/lib/pgsql/9.6/data
  Sudo systemctl start postgresql-9.6


Is this on the first VM you showed previously or the new one you said 
you where going to create?




Erros:

Unit postgresql-9.6.service entered failed state.
Abr 21 07:50:11 localhost.localdomain systemd[1]: postgresql-9.6.service
failed.
Abr 21 07:50:11 localhost.localdomain polkitd[693]: Unregistered
Authentication Agent for unix-process:30276:3843896 (system bus name
:1.486, object p
lines 2341-2377/2377 (END)


/var/lib/pgsql/9.6/data/" is missing or empty.


Is it empty, because before it was not?



Apr 21 07:50:11 localhost postgresql96-check-db-dir: Use
"/usr/pgsql-9.6/bin/postgresql96-setup initdb" to initialize the
database cluster.
Apr 21 07:50:11 localhost postgresql96-check-db-dir: See
/usr/share/doc/postgresql96/README.rpm-dist for more information.
Apr 21 07:50:11 localhost systemd: postgresql-9.6.service: control
process exited, code=exited status=1
Apr 21 07:50:11 localhost systemd: Failed to start PostgreSQL 9.6
database server.
Apr 21 07:50:11 localhost systemd: Unit postgresql-9.6.service entered
failed state.
Apr 21 07:50:11 localhost systemd: postgresql-9.6.service failed.





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

Re: [GENERAL] Large data and slow queries

[Andrew Staller]

Samuel,

Short answer to your questions:  (1) TimescaleDB and CitusDB are focusing
on solving different problems, and (2) TimescaleDB is an Apache 2-licensed
extension to run in your Postgres database, not a fork or different system.

Longer answer to your first question:

>From what we've read and the conversations we've had with Citus (awesome
folks, btw), they're primarily solving a different problem -- OLAP queries
and distributed transactions -- while we are focused on time-series data
analysis.  As such, we haven't benchmarked against Citus and if we were to,
it would require some unrealistic finagling in order to make it an
apples-to-apples comparison.

As an example, their partitioning is based on a single primary key, while
all writes in their clustered version go to a cluster master (although
their hosted version of Citus MX changes this a bit).  We perform
two-dimensional partitioning using your primary key and time, with the
partitions automatically created/closed on time intervals and data volume
in order to keep the most recent partitioned chunks of data (and their
B-trees) in memory to support fast ingest for time-series data. (***) We
have only released our single-node version, but the clustering will allow
you to write directly to any node.  Citus has also decided only to support
the SQL queries they are optimized for, while we've made the decision to
support "full SQL" (even if some queries might not be fast).

*** We blogged yesterday about TimescaleDB's partitioning design choices in
more depth, if you are interested:
https://blog.timescale.com/time-series-data-why-and-how-to-u
se-a-relational-database-instead-of-nosql-d0cd6975e87c


On Thu, Apr 20, 2017 at 5:43 PM, Samuel Williams <
space.ship.travel...@gmail.com> wrote:

> Andrew, how would timescaledb compare to citus - and is timescaledb an
> extension to postgres or is it an entirely separate system?
>
> On 21 April 2017 at 02:44, Andrew Staller  wrote:
> > Awesome thread.
> >
> > Samuel,
> >
> > Just wanted you to be aware of the work we're doing at TimescaleDB
> > (http://www.timescale.com/), a time-series database extension for
> > PostgreSQL.
> >
> > Some of how we might help you:
> > - automatic partitioning by space (primary key - like country_id, for
> > instance) and time. This creates "chunks" of your data, right-sized by
> > volume and time constraints (which makes inserts fast at much greater
> scale
> > than Vanilla PostgreSQL - not sure if this is a problem for you)
> > - this will also help if your queries are selective on time and
> country_id
> > (per this example)
> > - the partitioning by time allows you to DROP old chunks without the need
> > for vacuums
> >
> > On Thu, Apr 20, 2017 at 8:30 AM, Vick Khera  wrote:
> >>
> >> I'm curious why you have so many partial indexes. Are you trying to make
> >> custom indexes per query? It seems to me you might want to consider
> making
> >> the indexes general, and remove the redundant ones (that have the same
> >> prefix list of indexed fields).
> >>
> >> Secondly your table is 102Gb. Clearly there's a lot of data here. How
> many
> >> rows does that take? I would further suggest that you partition this
> table
> >> such that there are no more than about 10 million rows per partition
> (I've
> >> done this by using a id % 100 computation). Maybe in your case it makes
> >> sense to partition it based on the "what" field, because it appears you
> are
> >> trying to do that with your partial indexes already.
> >>
> >> On Wed, Apr 19, 2017 at 10:11 PM, Samuel Williams
> >>  wrote:
> >>>
> >>> Okay, so after changing longitude/latitude to float4, and
> >>> re-organizing the table a bit, I got the query down from about 8
> >>> minutes to 40 seconds.
> >>>
> >>> The details are in the gist comments:
> >>> https://gist.github.com/ioquatix/bddda36d9e4ffaceb7a62d7b62259121
> >>>
> >>> Now, just need to get performance another 2 orders of magnitude
> >>> better. Can we make the index a bit more coarse grained, perhaps
> >>> convert long/lat to integers or something, use a hilbert index, use a
> >>> postgis index... ideas?
> >>>
> >>>
> >>> --
> >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >>> To make changes to your subscription:
> >>> http://www.postgresql.org/mailpref/pgsql-general
> >>
> >>
> >
> >
> >
> > --
> > TimescaleDB | Growth & Developer Evangelism
> > c: 908.581.9509
> >
> > 335 Madison Ave.
> > New York, NY 10017
> > www.timescale.com
> > https://github.com/timescale/timescaledb
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
TimescaleDB* | *Growth & Developer Evangelism
c: 908.581.9509 <(908)%20581-9509>

335 Madison Ave.
New York, NY 10017
www.timescale.com
https://github.com/timescale/timescaledb

Re: [GENERAL] Recover PostgreSQL database folder data

[Edson Lidorio]

On 21-04-2017 17:27, Scott Mead wrote:

chown *-R* postgres:postgres /var/lib/pgsql/9.6/data


Chown -R postgres: postgres /var/lib/pgsql/9.6/data
  Sudo systemctl start postgresql-9.6

Erros:

Unit postgresql-9.6.service entered failed state.
Abr 21 07:50:11 localhost.localdomain systemd[1]: postgresql-9.6.service 
failed.
Abr 21 07:50:11 localhost.localdomain polkitd[693]: Unregistered 
Authentication Agent for unix-process:30276:3843896 (system bus name 
:1.486, object p

lines 2341-2377/2377 (END)


/var/lib/pgsql/9.6/data/" is missing or empty.
Apr 21 07:50:11 localhost postgresql96-check-db-dir: Use 
"/usr/pgsql-9.6/bin/postgresql96-setup initdb" to initialize the 
database cluster.
Apr 21 07:50:11 localhost postgresql96-check-db-dir: See 
/usr/share/doc/postgresql96/README.rpm-dist for more information.
Apr 21 07:50:11 localhost systemd: postgresql-9.6.service: control 
process exited, code=exited status=1
Apr 21 07:50:11 localhost systemd: Failed to start PostgreSQL 9.6 
database server.
Apr 21 07:50:11 localhost systemd: Unit postgresql-9.6.service entered 
failed state.

Apr 21 07:50:11 localhost systemd: postgresql-9.6.service failed.

Re: [GENERAL] Recover PostgreSQL database folder data

[Adrian Klaver]

On 04/21/2017 01:14 PM, Edson Lidorio wrote:



On 21-04-2017 16:00, Adrian Klaver wrote:

Is that really the case?

Yes, I have already given permission on this folder and it does not
initialize.


You do not want to initialize the directory that was taken care of when 
you did:


3- I renamed the /var/lib/pgsql/9.6/data folder to date data_old and 
copied the old date folder


The files are there, the start script is just not able to read them 
because of permissions issues. You need to verify the permissions on the 
files in data/-




I'll try to make another copy in another vm.


Pretty sure you will end up at the same place.





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

Re: [GENERAL] Recover PostgreSQL database folder data

[Scott Mead]

On Fri, Apr 21, 2017 at 12:40 PM, Edson Lidorio 
wrote:

> Hi,
> There was a disaster in my development note. I was able to recover the
> data folder. PostgreSQL 9.6.2, was installed in Centos 7.
>
> Here are the procedures I'm trying to initialize Postgresql for me to do a
> backup.
>
> 1- I installed PostgreSQL 9.6.2 on a VM with Centos 7.
> 2- I stopped the PostgreSQL service: sudo systemctl stop postgresql-9.6
> 3- I renamed the /var/lib/pgsql/9.6/data folder to date data_old and
> copied the old date folder
> 4- I gave permission in the folder date:
>  sudo chown postgres: postgres /var/lib/pgsql/9.6/data;
>

Your error is that you didn't 'chown *-R* postgres:postgres
/var/lib/pgsql/9.6/data'

--Scott


>  sudo chmod 700 /var/lib/pgsql/9.6/data
> 5 - I tried to start the service: sudo systemctl start postgresql-9.6
> It is generating the following errors:
>
> Abr 21 01:25:35 localhost.localdomain systemd[1]: Starting PostgreSQL 9.6
> database server...
> Abr 21 01:25:36 localhost.localdomain postgresql96-check-db-dir[19996]:
> cat: /var/lib/pgsql/9.6/data//PG_VER…ada
> Abr 21 01:25:36 localhost.localdomain postgresql96-check-db-dir[19996]:
> cat: /var/lib/pgsql/9.6/data//PG_VER…ada
> Abr 21 01:25:36 localhost.localdomain systemd[1]: postgresql-9.6.service:
> control process exited, code=ex...us=1
> Abr 21 01:25:36 localhost.localdomain systemd[1]: Failed to start
> PostgreSQL 9.6 database server.
> Abr 21 01:25:36 localhost.localdomain systemd[1]: Unit
> postgresql-9.6.service entered failed state.
> Abr 21 01:25:36 localhost.localdomain systemd[1]: postgresql-9.6.service
> failed.
> Hint: Some lines were ellipsized, use -l to show in full.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

Re: [GENERAL] Recover PostgreSQL database folder data

[John R Pierce]

On 4/21/2017 1:14 PM, Edson Lidorio wrote:



On 21-04-2017 16:00, Adrian Klaver wrote:

Is that really the case?
Yes, I have already given permission on this folder and it does not 
initialize.
I'll try to make another copy in another vm. 


who owns the files IN the folder? try chown -R postgres.postgres 
/path/to/data



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Recover PostgreSQL database folder data

[Edson Lidorio]

On 21-04-2017 16:00, Adrian Klaver wrote:

Is that really the case?
Yes, I have already given permission on this folder and it does not 
initialize.

I'll try to make another copy in another vm.


--
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] Recover PostgreSQL database folder data

[John R Pierce]

On 4/21/2017 12:00 PM, Adrian Klaver wrote:

Apr 21 04:01:48 localhost postgresql96-check-db-dir: cat:
/var/lib/pgsql/9.6/data//PG_VERSION: Permissão negada


So you got a permissions error when the script was trying to read 
PG_VERSION.


The suspicious part is this:
/var/lib/pgsql/9.6/data//PG_VERSION

in particular the //

Is that really the case? 



unix/linux seems quite happy to ignore extra /'s ...

# ls -l /var/lib/pgsql/9.3/data//PG_VERSION
-rw---. 1 postgres postgres 4 May 22  2014 
/var/lib/pgsql/9.3/data//PG_VERSION



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Recover PostgreSQL database folder data

[Adrian Klaver]

On 04/21/2017 10:09 AM, Edson Lidorio wrote:



On 21-04-2017 13:48, Adrian Klaver wrote:

On 04/21/2017 09:40 AM, Edson Lidorio wrote:

Hi,
There was a disaster in my development note. I was able to recover the
data folder. PostgreSQL 9.6.2, was installed in Centos 7.

Here are the procedures I'm trying to initialize Postgresql for me to do
a backup.

1- I installed PostgreSQL 9.6.2 on a VM with Centos 7.
2- I stopped the PostgreSQL service: sudo systemctl stop postgresql-9.6
3- I renamed the /var/lib/pgsql/9.6/data folder to date data_old and
copied the old date folder
4- I gave permission in the folder date:
 sudo chown postgres: postgres /var/lib/pgsql/9.6/data;
 sudo chmod 700 /var/lib/pgsql/9.6/data
5 - I tried to start the service: sudo systemctl start postgresql-9.6
It is generating the following errors:

Abr 21 01:25:35 localhost.localdomain systemd[1]: Starting PostgreSQL
9.6 database server...
Abr 21 01:25:36 localhost.localdomain postgresql96-check-db-dir[19996]:
cat: /var/lib/pgsql/9.6/data//PG_VER…ada
Abr 21 01:25:36 localhost.localdomain postgresql96-check-db-dir[19996]:
cat: /var/lib/pgsql/9.6/data//PG_VER…ada
Abr 21 01:25:36 localhost.localdomain systemd[1]:
postgresql-9.6.service: control process exited, code=ex...us=1
Abr 21 01:25:36 localhost.localdomain systemd[1]: Failed to start
PostgreSQL 9.6 database server.
Abr 21 01:25:36 localhost.localdomain systemd[1]: Unit
postgresql-9.6.service entered failed state.
Abr 21 01:25:36 localhost.localdomain systemd[1]: postgresql-9.6.service
failed.
Hint: Some lines were ellipsized, use -l to show in full.


What do you see if you do the above, add -l to the systemctl command?

● postgresql-9.6.service loaded failed failedPostgreSQL 9.6 database
server


What does the system log show?
Apr 21 04:01:01 localhost systemd: Started Session 53 of user root.
Apr 21 04:01:01 localhost systemd: Starting Session 53 of user root.
Apr 21 04:01:48 localhost systemd: Starting PostgreSQL 9.6 database
server...
Apr 21 04:01:48 localhost postgresql96-check-db-dir: cat:
/var/lib/pgsql/9.6/data//PG_VERSION: Permissão negada
Apr 21 04:01:48 localhost postgresql96-check-db-dir: cat:
/var/lib/pgsql/9.6/data//PG_VERSION: Permissão negada


So you got a permissions error when the script was trying to read 
PG_VERSION.


The suspicious part is this:
/var/lib/pgsql/9.6/data//PG_VERSION

in particular the //

Is that really the case?



Apr 21 04:01:48 localhost postgresql96-check-db-dir: An old version of
the database format was found.
Apr 21 04:01:48 localhost postgresql96-check-db-dir: You need to dump
and reload before using PostgreSQL 9.6.
Apr 21 04:01:48 localhost postgresql96-check-db-dir: See
/usr/share/doc/postgresql96/README.rpm-dist for more information.


Have to believe the above is a consequence of the permissions error. In 
the event it is not:


1) What are the contents of PG_VERSION for data_old versus data?

2) Did you use the same repos in the VM that you used in the original 
machine?



Apr 21 04:01:48 localhost systemd: postgresql-9.6.service: control
process exited, code=exited status=1
Apr 21 04:01:48 localhost systemd: Failed to start PostgreSQL 9.6
database server.
Apr 21 04:01:48 localhost systemd: Unit postgresql-9.6.service entered
failed state.
Apr 21 04:01:48 localhost systemd: postgresql-9.6.service failed.
Apr 21 04:02:07 localhost gnome-session: (nautilus:17753): Gtk-WARNING
**: gtk_widget_size_allocate(): attempt to allocate widget with width
-15 and height 34
Apr 21 04:02:57 localhost gnome-session: (nautilus:17753): Gtk-WARNING
**: gtk_widget_size_allocate(): attempt to allocate widget with width
-15 and height 34
Apr 21 04:03:00 localhost gnome-session: (nautilus:17753): Gtk-WARNING
**: gtk_widget_size_allocate(): attempt to allocate widget with width
-15 and height 34















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

Re: [GENERAL] pg_basebackup ----xlog-method=stream

[Adrian Klaver]

On 04/21/2017 11:02 AM, Ian Harding wrote:

I used this command to set up a streaming replica and it worked perfectly.


Was that the full command?

If not can you show the full command you used?

Postgres version?



I tried to run it to create an online backup of the master on that
replica for backup purposes and it seems not to have worked as well.

I thought that streaming the WAL would eliminate the need to keep tons
of WAL around, that since they were streamed concurrently with the file
they would be in place.  However, I got

pg_basebackup: could not get transaction log end position from server:
ERROR:  requested WAL segment 0001496F00BF has already been
removed


Where was pg_basebackup running, on the master or somewhere else?

The pg_basebackup was from the same Postgres version as the master?



The ...backup file in the archive logs folder of the master looks like this"

# cat 0001496F00BF.00578DF8.backup
START WAL LOCATION: 496F/BF578DF8 (file 0001496F00BF)
STOP WAL LOCATION: 4971/11FC6528 (file 000149710011)
CHECKPOINT LOCATION: 496F/C1ACE0D8
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2017-04-21 01:08:38 PDT
LABEL: full-20170421
STOP TIME: 2017-04-21 06:31:28 PDT

so the "...already been removed" message is referring to the very first
file.

The files were definitely being streamed, I restarted and on the master
I see this:

[root@db20 ~]# ps aux | grep stream
postgres 113855  0.4  0.0 274955352 2932 ?  Ss   10:53   0:00
postgres: wal sender process postgres 192.168.4.21(51292) streaming
4972/7FBC22B8
root 115958  0.0  0.0 112652   956 pts/2S+   10:56   0:00 grep
--color=auto stream
postgres 142072  0.3  0.0 274955348 3160 ?  Ss   Apr20   3:47
postgres: wal sender process postgres 192.168.4.21(51240) streaming
4972/7FBC22B8


although the --verbose output from pg_basebackup mentions starting the
streaming, it doesn't mention the file names like it does for the
datadir files.

Am I misunderstanding how this works?  I have WAL archiving set up, so
the files are available, but I wanted them included in the backup.

Thanks!

- Ian



--
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] pg_basebackup ----xlog-method=stream

[Ian Harding]

I used this command to set up a streaming replica and it worked perfectly.

I tried to run it to create an online backup of the master on that replica
for backup purposes and it seems not to have worked as well.

I thought that streaming the WAL would eliminate the need to keep tons of
WAL around, that since they were streamed concurrently with the file they
would be in place.  However, I got

pg_basebackup: could not get transaction log end position from server:
ERROR:  requested WAL segment 0001496F00BF has already been
removed

The ...backup file in the archive logs folder of the master looks like this"

# cat 0001496F00BF.00578DF8.backup
START WAL LOCATION: 496F/BF578DF8 (file 0001496F00BF)
STOP WAL LOCATION: 4971/11FC6528 (file 000149710011)
CHECKPOINT LOCATION: 496F/C1ACE0D8
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2017-04-21 01:08:38 PDT
LABEL: full-20170421
STOP TIME: 2017-04-21 06:31:28 PDT

so the "...already been removed" message is referring to the very first
file.

The files were definitely being streamed, I restarted and on the master I
see this:

[root@db20 ~]# ps aux | grep stream
postgres 113855  0.4  0.0 274955352 2932 ?  Ss   10:53   0:00 postgres:
wal sender process postgres 192.168.4.21(51292) streaming 4972/7FBC22B8
root 115958  0.0  0.0 112652   956 pts/2S+   10:56   0:00 grep
--color=auto stream
postgres 142072  0.3  0.0 274955348 3160 ?  Ss   Apr20   3:47 postgres:
wal sender process postgres 192.168.4.21(51240) streaming 4972/7FBC22B8


although the --verbose output from pg_basebackup mentions starting the
streaming, it doesn't mention the file names like it does for the datadir
files.

Am I misunderstanding how this works?  I have WAL archiving set up, so the
files are available, but I wanted them included in the backup.

Thanks!

- Ian

Re: [GENERAL] Recover PostgreSQL database folder data

[Edson Lidorio]

On 21-04-2017 13:48, Adrian Klaver wrote:

On 04/21/2017 09:40 AM, Edson Lidorio wrote:

Hi,
There was a disaster in my development note. I was able to recover the
data folder. PostgreSQL 9.6.2, was installed in Centos 7.

Here are the procedures I'm trying to initialize Postgresql for me to do
a backup.

1- I installed PostgreSQL 9.6.2 on a VM with Centos 7.
2- I stopped the PostgreSQL service: sudo systemctl stop postgresql-9.6
3- I renamed the /var/lib/pgsql/9.6/data folder to date data_old and
copied the old date folder
4- I gave permission in the folder date:
 sudo chown postgres: postgres /var/lib/pgsql/9.6/data;
 sudo chmod 700 /var/lib/pgsql/9.6/data
5 - I tried to start the service: sudo systemctl start postgresql-9.6
It is generating the following errors:

Abr 21 01:25:35 localhost.localdomain systemd[1]: Starting PostgreSQL
9.6 database server...
Abr 21 01:25:36 localhost.localdomain postgresql96-check-db-dir[19996]:
cat: /var/lib/pgsql/9.6/data//PG_VER…ada
Abr 21 01:25:36 localhost.localdomain postgresql96-check-db-dir[19996]:
cat: /var/lib/pgsql/9.6/data//PG_VER…ada
Abr 21 01:25:36 localhost.localdomain systemd[1]:
postgresql-9.6.service: control process exited, code=ex...us=1
Abr 21 01:25:36 localhost.localdomain systemd[1]: Failed to start
PostgreSQL 9.6 database server.
Abr 21 01:25:36 localhost.localdomain systemd[1]: Unit
postgresql-9.6.service entered failed state.
Abr 21 01:25:36 localhost.localdomain systemd[1]: postgresql-9.6.service
failed.
Hint: Some lines were ellipsized, use -l to show in full.


What do you see if you do the above, add -l to the systemctl command?
● postgresql-9.6.service loaded failed failedPostgreSQL 9.6 database 
server


What does the system log show?
Apr 21 04:01:01 localhost systemd: Started Session 53 of user root.
Apr 21 04:01:01 localhost systemd: Starting Session 53 of user root.
Apr 21 04:01:48 localhost systemd: Starting PostgreSQL 9.6 database 
server...
Apr 21 04:01:48 localhost postgresql96-check-db-dir: cat: 
/var/lib/pgsql/9.6/data//PG_VERSION: Permissão negada
Apr 21 04:01:48 localhost postgresql96-check-db-dir: cat: 
/var/lib/pgsql/9.6/data//PG_VERSION: Permissão negada
Apr 21 04:01:48 localhost postgresql96-check-db-dir: An old version of 
the database format was found.
Apr 21 04:01:48 localhost postgresql96-check-db-dir: You need to dump 
and reload before using PostgreSQL 9.6.
Apr 21 04:01:48 localhost postgresql96-check-db-dir: See 
/usr/share/doc/postgresql96/README.rpm-dist for more information.
Apr 21 04:01:48 localhost systemd: postgresql-9.6.service: control 
process exited, code=exited status=1
Apr 21 04:01:48 localhost systemd: Failed to start PostgreSQL 9.6 
database server.
Apr 21 04:01:48 localhost systemd: Unit postgresql-9.6.service entered 
failed state.

Apr 21 04:01:48 localhost systemd: postgresql-9.6.service failed.
Apr 21 04:02:07 localhost gnome-session: (nautilus:17753): Gtk-WARNING 
**: gtk_widget_size_allocate(): attempt to allocate widget with width 
-15 and height 34
Apr 21 04:02:57 localhost gnome-session: (nautilus:17753): Gtk-WARNING 
**: gtk_widget_size_allocate(): attempt to allocate widget with width 
-15 and height 34
Apr 21 04:03:00 localhost gnome-session: (nautilus:17753): Gtk-WARNING 
**: gtk_widget_size_allocate(): attempt to allocate widget with width 
-15 and height 34














--
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] Recover PostgreSQL database folder data

[Adrian Klaver]

On 04/21/2017 09:40 AM, Edson Lidorio wrote:

Hi,
There was a disaster in my development note. I was able to recover the
data folder. PostgreSQL 9.6.2, was installed in Centos 7.

Here are the procedures I'm trying to initialize Postgresql for me to do
a backup.

1- I installed PostgreSQL 9.6.2 on a VM with Centos 7.
2- I stopped the PostgreSQL service: sudo systemctl stop postgresql-9.6
3- I renamed the /var/lib/pgsql/9.6/data folder to date data_old and
copied the old date folder
4- I gave permission in the folder date:
 sudo chown postgres: postgres /var/lib/pgsql/9.6/data;
 sudo chmod 700 /var/lib/pgsql/9.6/data
5 - I tried to start the service: sudo systemctl start postgresql-9.6
It is generating the following errors:

Abr 21 01:25:35 localhost.localdomain systemd[1]: Starting PostgreSQL
9.6 database server...
Abr 21 01:25:36 localhost.localdomain postgresql96-check-db-dir[19996]:
cat: /var/lib/pgsql/9.6/data//PG_VER…ada
Abr 21 01:25:36 localhost.localdomain postgresql96-check-db-dir[19996]:
cat: /var/lib/pgsql/9.6/data//PG_VER…ada
Abr 21 01:25:36 localhost.localdomain systemd[1]:
postgresql-9.6.service: control process exited, code=ex...us=1
Abr 21 01:25:36 localhost.localdomain systemd[1]: Failed to start
PostgreSQL 9.6 database server.
Abr 21 01:25:36 localhost.localdomain systemd[1]: Unit
postgresql-9.6.service entered failed state.
Abr 21 01:25:36 localhost.localdomain systemd[1]: postgresql-9.6.service
failed.
Hint: Some lines were ellipsized, use -l to show in full.


What do you see if you do the above, add -l to the systemctl command?

What does the system log show?










--
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] Recover PostgreSQL database folder data

[Edson Lidorio]

Hi,
There was a disaster in my development note. I was able to recover the 
data folder. PostgreSQL 9.6.2, was installed in Centos 7.


Here are the procedures I'm trying to initialize Postgresql for me to do 
a backup.


1- I installed PostgreSQL 9.6.2 on a VM with Centos 7.
2- I stopped the PostgreSQL service: sudo systemctl stop postgresql-9.6
3- I renamed the /var/lib/pgsql/9.6/data folder to date data_old and 
copied the old date folder

4- I gave permission in the folder date:
 sudo chown postgres: postgres /var/lib/pgsql/9.6/data;
 sudo chmod 700 /var/lib/pgsql/9.6/data
5 - I tried to start the service: sudo systemctl start postgresql-9.6
It is generating the following errors:

Abr 21 01:25:35 localhost.localdomain systemd[1]: Starting PostgreSQL 
9.6 database server...
Abr 21 01:25:36 localhost.localdomain postgresql96-check-db-dir[19996]: 
cat: /var/lib/pgsql/9.6/data//PG_VER…ada
Abr 21 01:25:36 localhost.localdomain postgresql96-check-db-dir[19996]: 
cat: /var/lib/pgsql/9.6/data//PG_VER…ada
Abr 21 01:25:36 localhost.localdomain systemd[1]: 
postgresql-9.6.service: control process exited, code=ex...us=1
Abr 21 01:25:36 localhost.localdomain systemd[1]: Failed to start 
PostgreSQL 9.6 database server.
Abr 21 01:25:36 localhost.localdomain systemd[1]: Unit 
postgresql-9.6.service entered failed state.
Abr 21 01:25:36 localhost.localdomain systemd[1]: postgresql-9.6.service 
failed.

Hint: Some lines were ellipsized, use -l to show in full.



--
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] full text search on hstore or json with materialized view?

[George Neuner]

On Thu, 20 Apr 2017 07:56:18 -0700, Rj Ewing 
wrote:

>On Wed, Apr 19, 2017 at 6:44 PM, George Neuner  wrote:
>>
>> If you can restrict the FTS query to certain keys:
>>
>>   SELECT id FROM mytable
>> WHERE tsquery( ... ) @@ to_tsvector(v)
>> AND k IN ( ... )
>> GROUP BY id
>>
>>   [note: according to David Rowley, GROUP BY may be parallelized
>>  whereas  DISTINCT currently cannot be.]
>>
>> then given an index on 'k' it may be much faster than just the FTS
>> query alone.  Subject to key variability, it also may be improved by
>> table partitioning to reduce the search space.
>>
>> If the FTS query is key restricted, you can parallelize either on the
>> client or on the server.  If the FTS query is not key restricted, you
>> pretty much are limited to server side (and 9.6 or later).
>>
>> ?I'll look into parallelism if we can't get the performance we need.
>
>What do you mean if I can restrict the FTS query to certain keys? I'm not
>a sql expert, but it seems like the above query would match multiple keys
>to 1 tsquery value


You weren't specific as to the types of queries you wanted ... you
mentioned somewhere higher up in the discussion:

> ... a basic full text query on 44 million row is taking aproxx. 20ms.

That implied you wanted to FTS search every row.  Only later did you
give an example that tied FTS patterns to particular keys.  Until you
did that, there was no reason to assume the FTS search was targeted -
you might have wanted e.g., records where *any* k:v value matched the
FTS pattern.

[The take away here is: "try to be as specific as possible". 8-) ]


Obviously you can associate a FTS pattern with a particular key value
- just AND the conditions in the WHERE or HAVING clauses.

But be aware that, in general, the more conditions you place on a
query, the slower it runs.


George



-- 
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] full text search on hstore or json with materialized view?

[George Neuner]

On Thu, 20 Apr 2017 08:50:31 -0700, Rj Ewing 
wrote:

>On Wed, Apr 19, 2017 at 9:55 PM, George Neuner  wrote:
>
>> ... Since you are *testing* with 1M records (that
>> create 44M k:v shards), I am assuming you will need to deal with much
>> more than that in deployment.  And if you think you need FTS, then you
>> must be expecting more than simple word matches [as below], else you
>> might do something simpler like
>>
>>   SELECT ...
>> WHERE val ILIKE 
>
>the 1M records would most likely be the max. On average the tables would
>have more like 100,000 records each.

Ok, so my assumption was way off ... you should be able to achieve the
timing you want with appropriate indexing. 

>from my understanding, *ILIKE* doesn't do any text normalization, which is
>something we would like to have.

Right. If you want rooting/stemming or dictionary translation, then
you do need to use FTS.


>> >how would I write an AND query that filtered on 2 separate keys from the
>> >samples_lg_txt table?
>> >
>> >something like:
>> >
>> > SELECT COUNT(*) FROM samples WHERE id IN ( SELECT DISTINCT(s.id) FROM
>> > samples_lg_txt s JOIN keys k ON s.key = k.id WHERE (*name = 'key1' AND
>> > tsv @@ to_tsquery('value1')) AND (name = 'key2' AND tsv @@
>> > to_tsquery('value2'))*;
>>
>> You're overthinking it
>>
>>   SELECT count(distinct s.id)
>> FROM  samples_lg_txt AS s
>> JOIN  keys AS k ON k.id = s.key
>> WHERE (k.name = 'key1' AND s.tsv @@ to_query('value1')
>>OR (k.name = 'key2' AND s.tsv @@ to_query('value2')
>
>but that is an OR query, I'm trying to do an AND query.

Sorry, I missed the AND in your original query.  Still the nested
SELECT is unnecessary.

Postgresql's planner/optimizer is pretty smart, and probably would
collapse your code into mine (modulo the AND/OR goof), but I prefer
not to rely on the planner to be smart ... that gets you into trouble
when you have to switch between DBMS.


>> There's actually no need to join if you can use the key name instead
>> of an integer id.  You can FK on strings, so you can still maintain an
>> identity table of keys.  E.g.,
>>
>> > id | integer   |
>> > key| vchar(32) | FK key(name) ...
>> > val| text  |
>> > tsv| tsvector  |
>>
>>
>> Then the query could be just
>>
>>   SELECT count(distinct id)
>> FROM  samples_lg_txt
>> WHERE (key = 'key1' AND tsv @@ to_query('value1')
>>OR (key = 'key2' AND tsv @@ to_query('value2')
>>
>
>?this would make queries simpler?. 

Yes - it eliminates the joins, and the query runs on a single table.

>I guess a disadvantage to using a string
>for the key is that the db size would be larger, and thus not as likely to
>fit the entire table in ram. If there are only 63 keys across 44M rows, it
>seems that storing an smallint would take less space then storing the
>string.

Maybe.  Using the integer FK reduces the table size, but it requires a
join with the foreign table.  A join of two tables requires indexes
for the join columns on both tables [which may or may not already
exist], and produces [variously] a temporary hash or key relation
table that represents the rows of the "joined" table.  These temporary
structures can grow very large and may have to spill onto disk.

You can somewhat control that with the work_mem setting.  But remember
that the setting applies to every operation of every concurrent query
... so setting work_mem very high can backfire.


So saving one place can cost you in another.  TANSTAAFL.


>I don't really have a need for the identity table of keys. It's only
>purpose was to shrink the database size.
>
>Thanks again for the detailed responses!


George



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

[GENERAL] Limiting the amount of data in a variable when logging slow queries

[Brett Delle Grazie]

Hi,

We've enabled the slow query log and have a particular query that is
executing an insert against a table containing a binary array column.

Given the content being stored is upwards of 100MB+ this leads to very long
log messages for the slow query log.

Is there a way of having the slow query log only output the first 'n' bytes
of a byte array and/or exempting this particular insert?

Thanks,

-- 
Kind regards,

Brett Delle Grazie

Re: [GENERAL] pgAdmin4 needs information of v10 SCRAM authentication

[Akshay Joshi]

On Fri, Apr 21, 2017 at 12:20 PM, Michael Paquier  wrote:

> On Fri, Apr 21, 2017 at 3:43 PM, Akshay Joshi
>  wrote:
> >Thanks Michael, will check this.
>
> One thing I forgot to mention... Both StoredKey and ServerKey are now
> encoded in hex, but there is still an open item related to the
> handling of psql's \password on which I have written a patch to switch
> their encoding to base64 for simplicity. Not sure what is Heikki's
> take on the matter, but I would recommend to be careful about that. My
> last set of patches is here:
> https://www.postgresql.org/message-id/CAB7nPqSbsCBCxy8-
> DtwzRxYgTnbGUtY4uFEkLQhG=R=uo=g...@mail.gmail.com


Thanks Michael

>
> --
> Michael
>



-- 
*Akshay Joshi*
*Principal Software Engineer *



*Phone: +91 20-3058-9517Mobile: +91 976-788-8246*

Re: [GENERAL] pgAdmin4 needs information of v10 SCRAM authentication

[Michael Paquier]

On Fri, Apr 21, 2017 at 3:43 PM, Akshay Joshi
 wrote:
>Thanks Michael, will check this.

One thing I forgot to mention... Both StoredKey and ServerKey are now
encoded in hex, but there is still an open item related to the
handling of psql's \password on which I have written a patch to switch
their encoding to base64 for simplicity. Not sure what is Heikki's
take on the matter, but I would recommend to be careful about that. My
last set of patches is here:
https://www.postgresql.org/message-id/CAB7nPqSbsCBCxy8-DtwzRxYgTnbGUtY4uFEkLQhG=R=uo=g...@mail.gmail.com
-- 
Michael


-- 
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] pgAdmin4 needs information of v10 SCRAM authentication

[Akshay Joshi]

On Fri, Apr 21, 2017 at 12:07 PM, Michael Paquier  wrote:

> On Fri, Apr 21, 2017 at 3:27 PM, Akshay Joshi
>  wrote:
> > from passlib.hash import scram
> > hash = scram.encrypt(data['newPassword']) -- This function provide
> password for all the supported digest like [md5, sha-1, sha-256, sha-512].
> Didn't work I have tried with all the passwords.
> > test = scram.extract_digest_info(hash, "sha-256") -- This function
> extract info for specified digest "sha-256". I have retrieve the password
> which was in hexadecimal. Didn't work as well.
> >
> > Now I am stuck here and no clue how to encrypt/decrypt the password for
> SCRAM authentication. Can someone guide me out here.
>
> Here you go:
> https://www.postgresql.org/message-id/76ac7e67-4e3a-f4df-
> e087-fbac90151...@iki.fi


   Thanks Michael, will check this.

>
> --
> Michael
>



-- 
*Akshay Joshi*
*Principal Software Engineer *



*Phone: +91 20-3058-9517Mobile: +91 976-788-8246*

Re: [GENERAL] pgAdmin4 needs information of v10 SCRAM authentication

[Michael Paquier]

On Fri, Apr 21, 2017 at 3:27 PM, Akshay Joshi
 wrote:
> from passlib.hash import scram
> hash = scram.encrypt(data['newPassword']) -- This function provide password 
> for all the supported digest like [md5, sha-1, sha-256, sha-512]. Didn't work 
> I have tried with all the passwords.
> test = scram.extract_digest_info(hash, "sha-256") -- This function extract 
> info for specified digest "sha-256". I have retrieve the password which was 
> in hexadecimal. Didn't work as well.
>
> Now I am stuck here and no clue how to encrypt/decrypt the password for SCRAM 
> authentication. Can someone guide me out here.

Here you go:
https://www.postgresql.org/message-id/76ac7e67-4e3a-f4df-e087-fbac90151...@iki.fi
-- 
Michael


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

[GENERAL] pgAdmin4 needs information of v10 SCRAM authentication

[Akshay Joshi]

Hi All

We are into development phase where we are trying to incorporate the v10
changes into pgAdmin4. v10 added support for the SCRAM authentication into
database server, so pgAdmin4 needs to incorporate that feature(for 'Change
Password'). Now problem I am facing is, unable to find correct set of
python api's which I can use to perform SCRAM encryption/decryption through
pgAdmin4 (Most of you already know that pgAdmin4 is re-written in Python
and Web technologies).

I have googled for how to encrypt password for scram and found https://
passlib.readthedocs.io/en/1.6.2/lib/passlib.hash.scram.html?highlight=scram#
passlib.hash.scram . I have tried below logic to encrypt the password:


   - from passlib.hash import scram
   - hash = scram.encrypt(data['newPassword']) -- This function
provide password for all the supported digest like [md5, sha-1,
sha-256, sha-512]. Didn't work I have tried with all the passwords.
   - test = scram.extract_digest_info(hash, "sha-256") -- This
function extract info for specified digest "sha-256". I have retrieve
the password which was in hexadecimal. Didn't work as well.

Now I am stuck here and no clue how to encrypt/decrypt the password
for SCRAM authentication. Can someone guide me out here.

-- 
*Akshay Joshi*
*Principal Software Engineer *



*Phone: +91 20-3058-9517Mobile: +91 976-788-8246*