Snapshot recovery or rolling back commited

[Marc-Antoine Nüssli]

Hi there,

I saw there was a question about a similar topic recently but my use case
is quite different so there may be a different answer.
Roughly, I have a database which is updated by a single stream of updates
(through jdbc), so I have a single write transaction at any time. However,
sometimes I need to cancel some of the last updates in order to re-apply
different updates, but the readers should always be able to query the most
up-to-date state, including updates that could potentially be cancelled
afteward.
In other words, I need to be able, at any time, to rollback the last
updates up to a certain point (which is moving but always known) and to be
able to query the most up-to-date state (including updates that could be
rollbacked later)
Putting differently, I need two version of the same database, "consistent"
and "latest", with the "consistent" version being some updates behind
latest (or sometimes at the same state) and sometimes the "latest" version
must be restored back to the "consistent" version.

An approach would be to have a single transaction for the updates with a
"moving" savepoint, so that we can always rollback to the last "correct"
state. But, as far as I know, there is no way to query the updated snapshot
of an uncommitted transaction outside of it.
Indeed, we cannot do READ UNCOMMITED transaction and exporting transaction
snapshot  does not show the updates made by the original trnasaction.
The more I think and read about this, the more it seems this cannot be
achieved within a MVCC architecture...

I also thought of a different approach that would use PITR and WAL
features, but as far as I read about it, this would require a lot of
filesytem-level scripting as well as as restarting postgres each time we
want to restore to a previous state. This sounds quite difficult to
accomplish all of this automatically and ot very efficient for a production
system.

The last solution I thought of is to use an audit history (such as in
https://wiki.postgresql.org/wiki/Audit_trigger_91plus). Then I can use it
to rollback the last changes. And as I know up to which point I want to
restore, I can periodically clean the history to keep only the last
required changes. Currently, this is the only solution that seems doable to
mebut I'm not 100% sure that it would capture all possible cases and
that in some situations, restoring from the audit history will not restore
to the exact same state that it was...

So, my question is do you think any of the proposed approaches is doable?
And if yes, which one and how?
Otherwise, do you have any thought on how to accomplish such a kind of
use-case using postgres?

Thanks in advance for your response!

Best regards,
Marc-Antoine Nüssli

Re: Snapshot recovery or rolling back commited

[rob stone]

Hello Marc-Antoine,

On Thu, 2018-03-15 at 10:43 +0100, Marc-Antoine Nüssli wrote:
> Hi there,
> 
> I saw there was a question about a similar topic recently but my use
> case is quite different so there may be a different answer.
> Roughly, I have a database which is updated by a single stream of
> updates (through jdbc), so I have a single write transaction at any
> time. However, sometimes I need to cancel some of the last updates in
> order to re-apply different updates, but the readers should always be
> able to query the most up-to-date state, including updates that could
> potentially be cancelled afteward.
> In other words, I need to be able, at any time, to rollback the last
> updates up to a certain point (which is moving but always known) and
> to be able to query the most up-to-date state (including updates that
> could be rollbacked later) 
> Putting differently, I need two version of the same database,
> "consistent" and "latest", with the "consistent" version being some
> updates behind latest (or sometimes at the same state) and sometimes
> the "latest" version must be restored back to the "consistent"
> version.
> 
> An approach would be to have a single transaction for the updates
> with a "moving" savepoint, so that we can always rollback to the last
> "correct" state. But, as far as I know, there is no way to query the
> updated snapshot of an uncommitted transaction outside of it. 
> Indeed, we cannot do READ UNCOMMITED transaction and exporting
> transaction snapshot  does not show the updates made by the original
> trnasaction.
> The more I think and read about this, the more it seems this cannot
> be achieved within a MVCC architecture...
> 
> I also thought of a different approach that would use PITR and WAL
> features, but as far as I read about it, this would require a lot of
> filesytem-level scripting as well as as restarting postgres each time
> we want to restore to a previous state. This sounds quite difficult
> to accomplish all of this automatically and ot very efficient for a
> production system.
> 
> The last solution I thought of is to use an audit history (such as in
> https://wiki.postgresql.org/wiki/Audit_trigger_91plus). Then I can
> use it to rollback the last changes. And as I know up to which point
> I want to restore, I can periodically clean the history to keep only
> the last required changes. Currently, this is the only solution that
> seems doable to mebut I'm not 100% sure that it would capture all
> possible cases and that in some situations, restoring from the audit
> history will not restore to the exact same state that it was...
> 
> So, my question is do you think any of the proposed approaches is
> doable? And if yes, which one and how?
> Otherwise, do you have any thought on how to accomplish such a kind
> of use-case using postgres?
> 
> Thanks in advance for your response!
> 
> Best regards,
> Marc-Antoine Nüssli



Without seeing your schema but assuming that this stream of updates
only affects a single table, have you considered adding a column type
boolean to that table default value true, and assuming that it could be
included in a surrogate primary key, when an "update" is to be
processed, lock that row, update the boolean to false and then insert a
new row?

You could then schedule a cron job to physically delete all rows where
the boolean is false and if required, insert them into an archive table
just in case you need to review the history.

This way, your application is useable 24/7.

Just a thought.

Cheers,
Rob

Re: How to monitor logical replication initial sync?

[bricklen]

On Wed, Mar 7, 2018 at 3:23 PM, Doug Gorley  wrote:

> Good day,
>
> How does one monitor the status or progress of an initial sync under
> logical replication?  For example:
>
> * I create a publication in database db_pub
> * I create a subscription in database db_sub
> * In 15 minutes I want to check an see that the initial sync is N% complete
>
> Is it possible to tell when the initial sync is complete, or better yet,
> how complete it is?
>

​This is a question I'm quite interested in as well (and one I do not have
an answer to).​

​Does anyone with more familiarity ​with logical replication have any
suggestions on how to determine the status of the initial sync?

Change ON UPDATE behavior of fkey

[Mike Rylander]

I'm wanting to change the ON UPDATE behavior of several foreign keys.
I know this has been "asked and answered" in the past, and I have a
query that will generate one-statement-per-fkey DDL, but while
tailoring that query I noticed that pg_constraint's confupdtype could
just be changed to 'c' directly.

So, my question is, will updating pg_constraint "just work" (for, say,
new sessions, after forcing clients to reconnect) or would there be a
data risk?

Thanks in advance,

--
Mike Rylander

psql output result

[Tiffany Thang]

Hi,
I have a SQL script that does some DDLs, inserts and counts.

The command I ran is
psql dbname -c "\i crscript.sql" > output.txt

In output.txt, I got something like
INSERT 0 1
INSERT 0 1
CREATE TABLE
INSERT 0 2
 count
---
 9
(1 row)


Is there a way to output the SQLs and DDLs so that I could easily identify
what statements were executed?

Thanks.

Re: psql output result

[Geoff Winkless]

On 15 March 2018 at 14:57, Tiffany Thang  wrote:

> Is there a way to output the SQLs and DDLs so that I could easily identify
> what statements were executed?
>
> ​
  -a, --echo-all   echo all input from script

Geoff​

Re: psql output result

[Adrian Klaver]

On 03/15/2018 07:57 AM, Tiffany Thang wrote:

Hi,
I have a SQL script that does some DDLs, inserts and counts.

The command I ran is
psql dbname -c "\i crscript.sql" > output.txt

In output.txt, I got something like
INSERT 0 1
INSERT 0 1
CREATE TABLE
INSERT 0 2
  count
---
  9
(1 row)


Is there a way to output the SQLs and DDLs so that I could easily 
identify what statements were executed?


aklaver@tito:~> psql -d test -U aklaver -a -f sql_test.sql
\pset null 'NULL'
Null display is "NULL".
CREATE TABLE tbl_test(fld_1 int, fld_2 varchar);
CREATE TABLE
INSERT INTO tbl_test VALUES (1, 'dog'), (2, 'cat');
INSERT 0 2
DROP TABLE tbl_test;
DROP TABLE



Thanks.




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

Re: wiki Disk Usage, table size: ERROR: could not open relation with OID 0

[Hans Schou]

On Thu, Mar 15, 2018 at 12:32 AM, Michael Paquier 
wrote:


> > I'm running version 9.1.9 so it should be working according to the
> > wiki.
>
> You should update and upgrade.  9.1 has fallen out of community support
>

I will recommend that to the database owner. Thanks

Re: wiki Disk Usage, table size: ERROR: could not open relation with OID 0

[Hans Schou]

On Thu, Mar 15, 2018 at 12:44 AM, Tom Lane  wrote:

>
> The query does fail on < 9.2, because on rows with no reltoastrelid
>

Thats, fine. I will live with that until upgrade.


> But hey, it's a wiki;
> if you feel more ambitious, edit away.
>

I tried but it said:
"The site you are trying to log in to (the postgresql wiki) requires a
cool-off period between account creation and logging in. Please try again
later, or contact the postgresql.org webmasters if you have an urgent need
to log in."

ERROR: right sibling's left-link doesn't match: block 5 links to 8 instead of expected 2 in index "pg_toast_2619_index"

[Raghavendra Rao J S V]

*Hi All,*


*We are facing below error in my postgres 9.2 production database. Please
help us how to resolve and why we are facing this issue and impact of the
issue. *


*ERROR:  right sibling's left-link doesn't match: block 5 links to 8
instead of expected 2 in index "pg_toast_2619_index"*


*CONTEXT:  automatic vacuum of table "qovr.pg_toast.pg_toast_2619"*


-- 
Regards,
Raghavendra Rao J S V

SELECT .. FOR UPDATE: find out who locked a row

[Enrico Thierbach]

Hello,

I am using `SELECT * FROM queue ... FOR UPDATE SKIP LOCKED` to implement 
a queueing system.


Now I wonder if it is possible, given the id of one of the locked rows 
in the queue table, to find out which connection/which transaction owns 
the lock.


Any help is greatly appreciated.

Thank you,
/eno

--
me at github: https://github.com/radiospiel
me at linked.in: https://www.linkedin.com/in/radiospiel

Re: SELECT .. FOR UPDATE: find out who locked a row

[pinker]

Check out here: https://wiki.postgresql.org/wiki/Lock_Monitoring



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

Re: SELECT .. FOR UPDATE: find out who locked a row

[David G. Johnston]

On Thu, Mar 15, 2018 at 1:30 PM, Enrico Thierbach  wrote:

> Now I wonder if it is possible, given the id of one of the locked rows in
> the queue table, to find out which connection/which transaction owns the
> lock
>
​I'd start here:​

https://www.postgresql.org/docs/10/static/view-pg-locks.html
​​

and the pg_stat_activity view:

https://www.postgresql.org/docs/10/static/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE

The system doesn't really understand your model ID/PK field - the system
views and catalogs using system identifiers.

David J.

Re: psql output result

[Tiffany Thang]

Thanks Geoff and Adrian!

On Thu, Mar 15, 2018 at 11:03 AM, Adrian Klaver 
wrote:

> On 03/15/2018 07:57 AM, Tiffany Thang wrote:
>
>> Hi,
>> I have a SQL script that does some DDLs, inserts and counts.
>>
>> The command I ran is
>> psql dbname -c "\i crscript.sql" > output.txt
>>
>> In output.txt, I got something like
>> INSERT 0 1
>> INSERT 0 1
>> CREATE TABLE
>> INSERT 0 2
>>   count
>> ---
>>   9
>> (1 row)
>>
>>
>> Is there a way to output the SQLs and DDLs so that I could easily
>> identify what statements were executed?
>>
>
> aklaver@tito:~> psql -d test -U aklaver -a -f sql_test.sql
> \pset null 'NULL'
> Null display is "NULL".
> CREATE TABLE tbl_test(fld_1 int, fld_2 varchar);
> CREATE TABLE
> INSERT INTO tbl_test VALUES (1, 'dog'), (2, 'cat');
> INSERT 0 2
> DROP TABLE tbl_test;
> DROP TABLE
>
>
>> Thanks.
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: SELECT .. FOR UPDATE: find out who locked a row

[Stephen Frost]

Greetings,

* Enrico Thierbach (e...@open-lab.org) wrote:
> I am using `SELECT * FROM queue ... FOR UPDATE SKIP LOCKED` to implement a
> queueing system.
> 
> Now I wonder if it is possible, given the id of one of the locked rows in
> the queue table, to find out which connection/which transaction owns the
> lock.

Sure, you can attempt to lock the record and then run pg_blocking_pids()
(in another session) against the pid which is trying to acquire the
lock.

Session #1:

Connect
SELECT * FROM queue ... FOR UPDATE SKIP LOCKED;
... gets back some id X
... waits

Session #2:

Connect
SELECT pg_backend_pid(); -- save this for the 3rd session
SELECT * FROM queue WHERE id = X FOR UPDATE;
... get blocked waiting for #1
... waits

Session #3:

SELECT pg_blocking_pids(SESSION_2_PID);
-- returns PID of Session #1

Obviously there's race conditions and whatnot (what happens if session
#1 releases the lock?), but that should work to figure out who is
blocking who.

If you're on a version of PG without pg_blocking_pids then you can look
in the pg_locks view, though that's a bit more annoying to decipher.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: SELECT .. FOR UPDATE: find out who locked a row

[Melvin Davidson]

On Thu, Mar 15, 2018 at 4:48 PM, Stephen Frost  wrote:

> Greetings,
>
> * Enrico Thierbach (e...@open-lab.org) wrote:
> > I am using `SELECT * FROM queue ... FOR UPDATE SKIP LOCKED` to implement
> a
> > queueing system.
> >
> > Now I wonder if it is possible, given the id of one of the locked rows in
> > the queue table, to find out which connection/which transaction owns the
> > lock.
>
> Sure, you can attempt to lock the record and then run pg_blocking_pids()
> (in another session) against the pid which is trying to acquire the
> lock.
>
> Session #1:
>
> Connect
> SELECT * FROM queue ... FOR UPDATE SKIP LOCKED;
> ... gets back some id X
> ... waits
>
> Session #2:
>
> Connect
> SELECT pg_backend_pid(); -- save this for the 3rd session
> SELECT * FROM queue WHERE id = X FOR UPDATE;
> ... get blocked waiting for #1
> ... waits
>
> Session #3:
>
> SELECT pg_blocking_pids(SESSION_2_PID);
> -- returns PID of Session #1
>
> Obviously there's race conditions and whatnot (what happens if session
> #1 releases the lock?), but that should work to figure out who is
> blocking who.
>
> If you're on a version of PG without pg_blocking_pids then you can look
> in the pg_locks view, though that's a bit more annoying to decipher.
>
> Thanks!
>
> Stephen
>

> Now I wonder if it is possible, given the id of one of the locked rows in
> the queue table, to find out which connection/which transaction owns

You have not specified which version of PostgreSQL, but try this query.

SELECT c.datname,
   c.pid as pid,
   c.client_addr,
   c.usename as user,
   c.query,
   c.wait_event,
   c.wait_event_type,
/*   CASE WHEN c.waiting = TRUE
THEN 'BLOCKED'
ELSE 'no'
END as waiting,
*/
  l.pid as blocked_by,
   c.query_start,
   current_timestamp - c.query_start as duration
  FROM pg_stat_activity c
  LEFT JOIN pg_locks l1 ON (c.pid = l1.pid and not l1.granted)
  LEFT JOIN pg_locks l2 on (l1.relation = l2.relation and l2.granted)
  LEFT JOIN pg_stat_activity l ON (l2.pid = l.pid)
  LEFT JOIN pg_stat_user_tables t ON (l1.relation = t.relid)
 WHERE pg_backend_pid() <> c.pid
ORDER BY datname,
 query_start;

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

Re: SELECT .. FOR UPDATE: find out who locked a row

[Enrico Thierbach]

Hi Melvin, hi everyone else,

thank you for your support, and for your query example. And oh yes, I 
forgot to mention the postgres version, which is 9.6; but if I find a 
solution which works in Version 10 then I could probably update.


I guess with your query I can figure out which connection holds a lock, 
but it seems I cannot correlate those locks to the rows which actually 
are locked, since `pg_locks` seems not to reference this in any way.


To be more explicit: I can find out about all locks in the current 
database that are held by other connections using


```
select l.* from pg_locks l
left join pg_database d on l.database=d.oid
where pid <> pg_backend_pid()
  and d.datname = current_database()
  and relation::regclass=''::regclass;
```

which, with one locked row, results in something like this:

```
locktype | database | relation | page | tuple | virtualxid | 
transactionid | classid | objid | objsubid | virtualtransaction |  pid  
| mode | granted | fastpath

--+--+--+--+---++---+-+---+--++---+--+-+--
 relation |   629976 |   638971 |  |   ||   
| |   |  | 3/983554   | 60515 | 
RowShareLock | t   | t

(1 row)
```

And here is where I am stuck.

`database`, `relation` and `pid` are accounted for - the only value I 
can't make sense of is the `virtualtransaction` entry.


I was hoping that objid or objsubid would contain the OID of the locked 
row, but obviously I miss a crucial piece of understanding :)

(Note that I tried this both WITH OID and without oid in my table.)

Best,
/eno



--
me at github: https://github.com/radiospiel
me at linked.in: https://www.linkedin.com/in/radiospiel

On 15 Mar 2018, at 22:12, Melvin Davidson wrote:

On Thu, Mar 15, 2018 at 4:48 PM, Stephen Frost  
wrote:



Greetings,

* Enrico Thierbach (e...@open-lab.org) wrote:
I am using `SELECT * FROM queue ... FOR UPDATE SKIP LOCKED` to 
implement

a

queueing system.

Now I wonder if it is possible, given the id of one of the locked 
rows in
the queue table, to find out which connection/which transaction owns 
the

lock.


Sure, you can attempt to lock the record and then run 
pg_blocking_pids()

(in another session) against the pid which is trying to acquire the
lock.

Session #1:

Connect
SELECT * FROM queue ... FOR UPDATE SKIP LOCKED;
... gets back some id X
... waits

Session #2:

Connect
SELECT pg_backend_pid(); -- save this for the 3rd session
SELECT * FROM queue WHERE id = X FOR UPDATE;
... get blocked waiting for #1
... waits

Session #3:

SELECT pg_blocking_pids(SESSION_2_PID);
-- returns PID of Session #1

Obviously there's race conditions and whatnot (what happens if 
session

#1 releases the lock?), but that should work to figure out who is
blocking who.

If you're on a version of PG without pg_blocking_pids then you can 
look

in the pg_locks view, though that's a bit more annoying to decipher.

Thanks!

Stephen



Now I wonder if it is possible, given the id of one of the locked 
rows in

the queue table, to find out which connection/which transaction owns


You have not specified which version of PostgreSQL, but try this 
query.


SELECT c.datname,
   c.pid as pid,
   c.client_addr,
   c.usename as user,
   c.query,
   c.wait_event,
   c.wait_event_type,
/*   CASE WHEN c.waiting = TRUE
THEN 'BLOCKED'
ELSE 'no'
END as waiting,
*/
  l.pid as blocked_by,
   c.query_start,
   current_timestamp - c.query_start as duration
  FROM pg_stat_activity c
  LEFT JOIN pg_locks l1 ON (c.pid = l1.pid and not l1.granted)
  LEFT JOIN pg_locks l2 on (l1.relation = l2.relation and l2.granted)
  LEFT JOIN pg_stat_activity l ON (l2.pid = l.pid)
  LEFT JOIN pg_stat_user_tables t ON (l1.relation = t.relid)
 WHERE pg_backend_pid() <> c.pid
ORDER BY datname,
 query_start;

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

Re: SELECT .. FOR UPDATE: find out who locked a row

[Stephen Frost]

Greetnigs,

* Enrico Thierbach (e...@open-lab.org) wrote:
> I guess with your query I can figure out which connection holds a lock, but
> it seems I cannot correlate those locks to the rows which actually are
> locked, since `pg_locks` seems not to reference this in any way.

What I gave you would work, or you could use the pgrowlocks extension:

https://www.postgresql.org/docs/current/static/pgrowlocks.html

Using pgrowlocks will be slow if you have a lot of records in the table
though, hence the other approach I mentioned since it sounds like you
know the ID that you're interested in.

Thanks!

Stephen


signature.asc
Description: PGP signature

Want to disable fully qualified table names on pg_dump in pg_dump (PostgreSQL) 9.6.8

[Foolish Ewe]

Hello All:

A number of our team members and I use pg_dump to export schema in an Ubuntu 
16.04 environment, I happen to have a postgress 9.6.4 server
that runs in a docker container, and in some cases I see the following select 
statement and fully qualified table names in the
CREATE TABLE and ALTER TABLE statements:

SELECT pg_catalog.set_config('search_path', '', false);

CREATE TABLE database_name.table_name

and likewise for ALTER TABLE.

But other users (who haven't updated their systems recently) do not see the 
SELECT statement and we see unqualified table names, e.g.:


CREATE TABLE table_name


These changes in format impact our workflows, we would prefer to have 
unqualified table names.

So far I've made some docker containers (to minimize interactions or config 
errors at the host level), and in the containers the
following pg_dump client versions all have this behavior.

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.4
-- Dumped by pg_dump version 9.6.8

-- Started on 2018-03-15 21:49:58 UTC

and

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.4
-- Dumped by pg_dump version 10.3 (Ubuntu 10.3-1.pgdg16.04+1)

-- Started on 2018-03-15 22:20:47 UTC


The pg_dump command we run is:

pg_dump --verbose -d postgresql://our_server_user:@localhost:5433/server 
--column-inserts -t table1 -t table2 ... -t tableN --schema-only

Does anyone have an idea of what might cause this?   Is there something we can 
do on the client side to avoid getting qualified table names?

With best regards:

Bill

Re: Want to disable fully qualified table names on pg_dump in pg_dump (PostgreSQL) 9.6.8

[Adrian Klaver]

On 03/15/2018 04:00 PM, Foolish Ewe wrote:

Hello All:

A number of our team members and I use pg_dump to export schema in an 
Ubuntu 16.04 environment, I happen to have a postgress 9.6.4 server
that runs in a docker container, and in some cases I see the following 
select statement and fully qualified table names in the

CREATE TABLE and ALTER TABLE statements:

SELECT pg_catalog.set_config('search_path', '', false);

CREATE TABLE database_name.table_name


I am pretty sure you are actually seeing:

CREATE TABLE schema_name.table_name



and likewise for ALTER TABLE.

But other users (who haven't updated their systems recently) do not see 
the SELECT statement and we see unqualified table names, e.g.:



CREATE TABLE table_name


These changes in format impact our workflows, we would prefer to have 
unqualified table names.






Does anyone have an idea of what might cause this?   Is there something 
we can do on the client side to avoid getting qualified table names?


The reason:

https://www.postgresql.org/about/news/1834/

"The purpose of this release is to address CVE-2018-1058, which 
describes how a user can create like-named objects in different schemas 
that can change the behavior of other users' queries and cause 
unexpected or malicious behavior, also known as a "trojan-horse" attack. "


More information

https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path

There is no flag to unset this.



With best regards:

Bill






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

Re: SELECT .. FOR UPDATE: find out who locked a row

[Melvin Davidson]

>I guess with your query I can figure out which connection holds a lock,
but it seems I cannot correlate those locks to the rows which actually are
locked, since pg_locks seems not to reference this in any way.



*Enrico,*



*FWIW, I really don't understand your need to identify the actual rows that
are locked. Once you have identified the query that is causing a block
(which is usually due to "Idle in Transaction"), AFAIK the only way to
remedy the problem is to kill the offending query, or wait for it to
complete. I am not aware of any way available to a user to "unlock"
individual rows". Indeed, if you could, it would probably lead to
corruption of some form.*


*BTW, the query I provided WILL work in version 10.  The commented section
was for v9.1 and prior, as "someone" felt it necessary to rename some
fields in pg_stat_activity*

*and remove/replace another field. Hopefully they will refrain from doing
so in the future, as it breaks queries and applications.*

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

Re: PgBackrest questions

[chiru r]

Greetings,

Please respond to my PgBackrest questions,if any one tested.

On Wed, Mar 14, 2018 at 10:54 PM, chiru r  wrote:

> Hi Stephen,
>
> Thank you very much for your quick reply.
>
>
> On Wed, Mar 14, 2018 at 6:17 PM, Stephen Frost  wrote:
>
>> Greetings,
>>
>> * chiru r (chir...@gmail.com) wrote:
>> > I am testing Pgbackrest and I have few questions.
>>
>> Great!
>>
>> > 1. I used postures user to perform backups and restores with Pgbackrest
>> > tool.
>> > The Trust authentication in pg_hba.conf file is working without issues.
>>
>> Please don't use 'trust'.
>>
>> > If I use md5 authentication in pg_hba_conf file and postgres user
>> password
>> > .pgpass file, the pgbackrest backup is failing.
>>
>> There really shouldn't ever be a need to use md5 authentication with
>> pgbackrest.  Instead, I'd strongly suggest you use 'peer'.  The 'peer'
>> method is perfectly safe as it depends on the authentication which
>> Unix provides, but it doesn't require a password or any of the
>> associated complications.  Note that 'trust' should *not* be used and
>> I'm glad to see that you're looking for alternatives to using 'trust'.
>>
>> Is there any alternative method other than Peer and Trust  to use with
> pgbackrest tool?.
>
>
>> > 2.  All the restores through the backrest is going to refer the  Wal
>> > archive files under archive//* directory, which are taken
>> from
>> > pgbackrest?
>>
>> When pgbackrest performs a restore, it will write out the recovery.conf
>> file for you which includes the restore command to pull the WAL from the
>> repo and stanza configured.  You shouldn't need to worry about where
>> those files are, specifically, coming from (and it's even possible that
>> it might change in the future...).  Is there a specific reason you're
>> asking?
>>
>> I am concerned about WAL archives because, I am planning to transfer my
> backups to Tape as soon as pgbackrest completes backup on disk.
> The restore of backups is going to be on different server.So i am
> concerned about recovery.
>
> Ex: Always I restore my production backups on DEV environment.
>
> And also I am planning to *remove *the Full,Diff and incremental backups
> on disk as soon as it transferred to tape. Is there any issue? since I am
> removing backups instead of Pg-backrest tool.
>
> 4. I observed that the *backup.info * and
> *backup.info.copy* files under stanza directory. I compared both files I
> did not see any difference.
> What is the reason to keep two files with same contents in same
> directory ?
>
> 5. The *backup.manifest *and *backup.manifest.copy* files exist under
> each backup directory(full/diff/incremental). What is the reason to keep
> to files in same directory?.
> What is the difference of backup.manifest file under *backup.history*
> directory and  under each backup directory?
>
> Thanks,
> Chiranjeevi
>
>

Re: SELECT .. FOR UPDATE: find out who locked a row

[Stephen Frost]

Greetings Melvin,

* Melvin Davidson (melvin6...@gmail.com) wrote:
> >I guess with your query I can figure out which connection holds a lock,
> but it seems I cannot correlate those locks to the rows which actually are
> locked, since pg_locks seems not to reference this in any way.
> 
> *FWIW, I really don't understand your need to identify the actual rows that
> are locked. Once you have identified the query that is causing a block
> (which is usually due to "Idle in Transaction"), AFAIK the only way to
> remedy the problem is to kill the offending query, or wait for it to
> complete. I am not aware of any way available to a user to "unlock"
> individual rows". Indeed, if you could, it would probably lead to
> corruption of some form.*

No, locks are not able to be released mid-transaction.  That said, it
can be difficult sometimes to determine which of the many sessions is
holding a lock on a specific row, hence the two approaches I provided,
which actually address the question which was raised.  While the
use-case might not be on completely solid ground here, I don't think
it's entirely unreasonable, so I don't think there's any need to tell
the OP that what they're asking for isn't really what they want, in this
case.

> *BTW, the query I provided WILL work in version 10.  The commented section
> was for v9.1 and prior, as "someone" felt it necessary to rename some
> fields in pg_stat_activity*
> *and remove/replace another field. Hopefully they will refrain from doing
> so in the future, as it breaks queries and applications.*

Changes will continue to be made between major versions of PostgreSQL
when they're deemed necessary; I'd suggest those applications be
prepared to adjust on a per-major-version basis when future changes
happen.  We do have quite a bit of discussion about changes which are
made and they are not done so without good justification, but they can
and do happen.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: PgBackrest questions

[Stephen Frost]

Greetings,

* chiru r (chir...@gmail.com) wrote:
> Please respond to my PgBackrest questions,if any one tested.

Please don't spam the lists repeatedly like this.  The responses to this
mailing list are provided by the community on a volunteer basis and
repeated emails are more likely to discourage responses than encourage
them.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: SELECT .. FOR UPDATE: find out who locked a row

[Melvin Davidson]

On Thu, Mar 15, 2018 at 10:14 PM, Stephen Frost  wrote:

> Greetings Melvin,
>
> * Melvin Davidson (melvin6...@gmail.com) wrote:
> > >I guess with your query I can figure out which connection holds a lock,
> > but it seems I cannot correlate those locks to the rows which actually
> are
> > locked, since pg_locks seems not to reference this in any way.
> >
> > *FWIW, I really don't understand your need to identify the actual rows
> that
> > are locked. Once you have identified the query that is causing a block
> > (which is usually due to "Idle in Transaction"), AFAIK the only way to
> > remedy the problem is to kill the offending query, or wait for it to
> > complete. I am not aware of any way available to a user to "unlock"
> > individual rows". Indeed, if you could, it would probably lead to
> > corruption of some form.*
>
> No, locks are not able to be released mid-transaction.  That said, it
> can be difficult sometimes to determine which of the many sessions is
> holding a lock on a specific row, hence the two approaches I provided,
> which actually address the question which was raised.  While the
> use-case might not be on completely solid ground here, I don't think
> it's entirely unreasonable, so I don't think there's any need to tell
> the OP that what they're asking for isn't really what they want, in this
> case.
>
> > *BTW, the query I provided WILL work in version 10.  The commented
> section
> > was for v9.1 and prior, as "someone" felt it necessary to rename some
> > fields in pg_stat_activity*
> > *and remove/replace another field. Hopefully they will refrain from doing
> > so in the future, as it breaks queries and applications.*
>
> Changes will continue to be made between major versions of PostgreSQL
> when they're deemed necessary; I'd suggest those applications be
> prepared to adjust on a per-major-version basis when future changes
> happen.  We do have quite a bit of discussion about changes which are
> made and they are not done so without good justification, but they can
> and do happen.
>
> Thanks!
>
> Stephen
>

> Changes will continue to be made between major versions of PostgreSQL
>when they're deemed necessary; I'

Yes, Stephen, I certainly understand making changes to system catalogs
_when necessary_.
That being said, the first change was the renaming of pid to procpid in
pg_stat_activity.
However, I contend that was more because someone felt that it was more to
make the column names
consistent across catalogs, rather than necessity. Care should be taken to
consider the need and
effect of changing EXISTING system columns. It may have been a mistake in
originally naming it,
but it was certainly working and not causing any problems at the time.
Just my personal opinion.



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

Re: PgBackrest questions

[Stephen Frost]

Greetings,

* chiru r (chir...@gmail.com) wrote:
> On Wed, Mar 14, 2018 at 6:17 PM, Stephen Frost  wrote:
> > * chiru r (chir...@gmail.com) wrote:
> > > I am testing Pgbackrest and I have few questions.
> >
> > Great!
> >
> > > 1. I used postures user to perform backups and restores with Pgbackrest
> > > tool.
> > > The Trust authentication in pg_hba.conf file is working without issues.
> >
> > Please don't use 'trust'.
> >
> > > If I use md5 authentication in pg_hba_conf file and postgres user
> > password
> > > .pgpass file, the pgbackrest backup is failing.
> >
> > There really shouldn't ever be a need to use md5 authentication with
> > pgbackrest.  Instead, I'd strongly suggest you use 'peer'.  The 'peer'
> > method is perfectly safe as it depends on the authentication which
> > Unix provides, but it doesn't require a password or any of the
> > associated complications.  Note that 'trust' should *not* be used and
> > I'm glad to see that you're looking for alternatives to using 'trust'.
>
> Is there any alternative method other than Peer and Trust  to use with
> pgbackrest tool?.

pgbackrest uses Perl's DBD::Pg to connect to PostgreSQL, which should
pick up on having a password provided through the environment variable
PGPASSWORD or that of DBI_PASS.  I would suggest setting one of those to
the password instead of trying to use .pgpass (though, given that
DBD::Pg uses libpq underneath, it's not clear to me off-hand why .pgpass
wouldn't work, but it's possible it doesn't because the username is
explicitly provided by pgbackrest to DBD::Pg's connect() routine).

> > > 2.  All the restores through the backrest is going to refer the  Wal
> > > archive files under archive//* directory, which are taken
> > from
> > > pgbackrest?
> >
> > When pgbackrest performs a restore, it will write out the recovery.conf
> > file for you which includes the restore command to pull the WAL from the
> > repo and stanza configured.  You shouldn't need to worry about where
> > those files are, specifically, coming from (and it's even possible that
> > it might change in the future...).  Is there a specific reason you're
> > asking?
>
> I am concerned about WAL archives because, I am planning to transfer my
> backups to Tape as soon as pgbackrest completes backup on disk.
> The restore of backups is going to be on different server.So i am concerned
> about recovery.
> 
> Ex: Always I restore my production backups on DEV environment.
> 
> And also I am planning to *remove *the Full,Diff and incremental backups on
> disk as soon as it transferred to tape. Is there any issue? since I am
> removing backups instead of Pg-backrest tool.

I would strongly recommend against trying to expire out the backups
yourself as you have to take a great deal of caution when it comes to
expiring the WAL- if you expire the wrong WAL then your backups will no
longer be valid.  pgbackrest has expiration options and an expiration
process which runs automatically and which will take care of cleaning
out any WAL which is no longer useful.

We do have plans for improving the options around expiration.

pgbackrest does have an option called '--archive-copy' which will copy
the WAL files required to restore a given backup (and only that WAL)
into the backup directory.  If you use that option and disable
compression then the backup directory for a full backup can be copied
to a new location directly and started as a PG server, where it will
replay all of the WAL.  If you're using differentials or incrementals,
then you would also need to use the --repo-hardlink option (and be on a
filesystem which supports hard-links) to allow you to do the same with
those backups.

In general, this is all a great deal simpler by making the repository
available for doing restores instead of having to ship things between
repos in the way you're describing.  It's also possible to perform a
regular filesystem-level backup and restore of the repository itself and
use that directly instead of trying to extract things from the repo.

> 4. I observed that the *backup.info * and
> *backup.info.copy* files under stanza directory. I compared both files I
> did not see any difference.
> What is the reason to keep two files with same contents in same
> directory ?

We don't want a crash or other issue to cause the backup.info file to be
only half-written, or similar, hence the reason for two copies.

> 5. The *backup.manifest *and *backup.manifest.copy* files exist under each
> backup directory(full/diff/incremental). What is the reason to keep to
> files in same directory?.

This is more-or-less the same reason as above.

> What is the difference of backup.manifest file under *backup.history*
> directory and  under each backup directory?

The backup.history file simply contains historical information about
past backups, in the form of the manifest files.  Not much is done with
them currently but we envision having a way to get statistical
information about 

Re: SELECT .. FOR UPDATE: find out who locked a row

[Stephen Frost]

Greetings Melvin,

* Melvin Davidson (melvin6...@gmail.com) wrote:
> On Thu, Mar 15, 2018 at 10:14 PM, Stephen Frost  wrote:
> > Changes will continue to be made between major versions of PostgreSQL
> > when they're deemed necessary; I'd suggest those applications be
> > prepared to adjust on a per-major-version basis when future changes
> > happen.  We do have quite a bit of discussion about changes which are
> > made and they are not done so without good justification, but they can
> > and do happen.
> 
> Yes, Stephen, I certainly understand making changes to system catalogs
> _when necessary_.
> That being said, the first change was the renaming of pid to procpid in
> pg_stat_activity.
> However, I contend that was more because someone felt that it was more to
> make the column names
> consistent across catalogs, rather than necessity. Care should be taken to
> consider the need and
> effect of changing EXISTING system columns. It may have been a mistake in
> originally naming it,
> but it was certainly working and not causing any problems at the time.

As mentioned earlier, care is taken when considering the need and effect
of changing existing system columns, but it can, and will, happen, and
therefore applications should be designed to cope with such changes
gracefully when they happen and tested thoroughly on each new major
version of PostgreSQL.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: SELECT .. FOR UPDATE: find out who locked a row

[Tom Lane]

Melvin Davidson  writes:
> Yes, Stephen, I certainly understand making changes to system catalogs
> _when necessary_.  That being said, the first change was the renaming of
> pid to procpid in pg_stat_activity.  However, I contend that was more
> because someone felt that it was more to make the column names
> consistent across catalogs, rather than necessity.

Please read all of
https://www.postgresql.org/message-id/flat/201106091554.p59Fso314146%40momjian.us
where this was discussed to death (and rejected), and then read all of
https://www.postgresql.org/message-id/flat/CAKq0gvK8PzMWPv19_o7CGg8ZQ0G%2BUuAWor5RrAg0SOmWTqqLwg%40mail.gmail.com
which is the thread in which the change was agreed to after all
(on the grounds that we were breaking backwards compatibility of
the view anyway with respect to other, more important, columns).

If you still feel that we make incompatible changes without adequate
consideration, that's your right, but you might want to consider
speaking up in some reasonable time frame, not six years later.
This could have been objected to as late as 9.2 beta, so it's not
like you need to be drinking from the pgsql-hackers firehose continually
in order to weigh in.  But 9.2 is not just released, it's EOL, so it's
really kinda late to be objecting.

regards, tom lane