Re: how to identify the timeline of specified recovery_target_time when do multiple PITR

[magodo]

On Thu, 2018-10-11 at 06:35 +0200, Laurenz Albe wrote:
> magodo wrote:
> > OK... Just take another example:
> > 
> >  A B
> > BASE-+-+--o1 (recover to
> > A)  1
> >  | |   C
> >  +.|...+---o2 (regret, recover to
> > B) 2
> >|   |
> >+...|..--o3 (regret again, recover to
> > C)  3
> >| 
> >+--
> > -- 4
> > 
> > 
> > Suppose I'm at "o3" and want to recover to point "C". Because I
> > want to
> > recover to the first timeline which covers this time point, it
> > means I
> > wish to recover to timeline 2.
> 
> Ah, I finally understand your question.
> 
> You assume tht timeline 1 and 2 have ended (that's how you drew it),
> and that consequently timeline 3 is the "earliest existing" timeline,
> so why doesn't PostgreSQL choose that one automatically?
> 
> Even though you drew it that way, timeline 1 and 2 have not ended, in
> a way.  There may be more on them.  How should PostgreSQL know what
> is
> the last WAL entry on a certain timeline?  For that, it would have to
> restore and examine *all* WAL segments on that timeline until that
> fails.
> 
> But that is unreasonable because of the potential amount of time
> and work involved.  Rather, PostgreSQL has to decide at the point
> where
> timeline 2 branches off which one it should follow.
> 
> Yours,
> Laurenz Albe

Thank God I finnally conveied my meaning :)

For one cluster, if , for example, it recovered to "A" at "o1", then
the switched WAL(in case of stop-then-recover) or .partial
corresponding WAL(in case of promote) is the last WAL of the timeline1,
and it makes no sense to consider timeline1 will continue grow after
"o1", because it has ended.

You meantioned: "There may be more on them.".

Would you please give me an example? I must have missed something
out...

---
Magodo

Advice on logging strategy

[Mike Martin]

I have a question on logging strategy

I have loggin set to
log_statement = 'all' on a network database with logging set to csv so I
can import it to a logging table

However the database is populated via a nightly routine downloading data
via REST APIusing prepared statements

This results in enormous log files which take ages to import using copy
becuase each execute statement is logged with the parameters chosen

Is there any way around this?

I cant find any way to filter dml statements

thanks

Re: Advice on logging strategy

[Rob Sargent]

> On Oct 11, 2018, at 4:26 AM, Mike Martin  wrote:
> 
> I have a question on logging strategy
> 
> I have loggin set to
> log_statement = 'all' on a network database with logging set to csv so I can 
> import it to a logging table
> 
> However the database is populated via a nightly routine downloading data via 
> REST APIusing prepared statements
> 
> This results in enormous log files which take ages to import using copy 
> becuase each execute statement is logged with the parameters chosen
> 
> Is there any way around this?
> 
> I cant find any way to filter dml statements
> 
> thanks
> 
Do you want all the log lines in you logging table?
There was a thread yesterday (10.Oct.2018) on COPY which mention the 
possibility of multiple processes COPYing to same table.

Re: Advice on logging strategy

[Mike Martin]

I suppose the ideal would be to log the prepared statement once and detail
only if error rather than one per execution

On Thu, 11 Oct 2018 at 11:33, Rob Sargent  wrote:

>
>
> > On Oct 11, 2018, at 4:26 AM, Mike Martin  wrote:
> >
> > I have a question on logging strategy
> >
> > I have loggin set to
> > log_statement = 'all' on a network database with logging set to csv so I
> can import it to a logging table
> >
> > However the database is populated via a nightly routine downloading data
> via REST APIusing prepared statements
> >
> > This results in enormous log files which take ages to import using copy
> becuase each execute statement is logged with the parameters chosen
> >
> > Is there any way around this?
> >
> > I cant find any way to filter dml statements
> >
> > thanks
> >
> Do you want all the log lines in you logging table?
> There was a thread yesterday (10.Oct.2018) on COPY which mention the
> possibility of multiple processes COPYing to same table.

RE: RHEL 7 (systemd) reboot

[Bryce Pepper]

Adrian,

Thanks for being willing to dig into this.  

You are correct there are other scripts being called from mine (delivered by 
BMC with their software).   In order to stay in support and work with their 
updates I use the vendor supplied scripts/programs.  

The Control-M product is installed on this single server and is broken down 
into the following parts:
Enterprise server with dedicated postgresql instance
Distributed datacenter with agent and dedicated postgresql instance
Linux datacenter with with agent and dedicated postgresql instance

To cut down on the noise, my post only focused on the "Distributed" side and 
shutdown process -- although the ControlM_Shutdown.service unit stop script 
manages all of the above components.

In the ControlM_Shutdown.service there is a requires statement identifying that 
 network must be available while this systemd unit runs.

You noticed that the eth0 disconnected in the /var/log/messages.   I showed 
that to highlight that the unit was not executing in the order I had intended, 
again refer to the requires statement.

The second shebang is from one of the invoked subscripts 
(stop_ctmdist_server.sh) and is the "main" shutdown sequence for the 
Distributed datacenter (I think the "SQL server" echo from BMC is because it 
can be configured with other databases and they use it in a generic term --- 
not meaning sqlserver from Microsoft).

The dbversion check is being used to verify pgsql instance for this datacenter 
is running and returns a non-zero return code if the instance is unreachable (I 
could use pg_isready or pg_ctl but would diverge further from the BMC supported 
technique).

You probably also noticed in the earlier posted shutdown service a requires of 
CTM_Postgre.service.  This was one of my attempts to ensure the instance was 
available by actually starting the instance outside of the BMC routines (if it 
is already running the BMC routines will not start -- the dbversion check is on 
the start side also).  I thought if I managed the postgresql instance outside 
of the product I could ensure it was running.  Unfortunately that didn't work 
as the instance shutdown on its own, presumably a resource (perhaps network) 
was terminated and postgresql shutdown.  

So to restate the original post...   It appears the postgresql instance is 
unavailable when the stop script runs.  

Thanks,
Bryce

[root@kccontrolmt01 ~]# systemctl --full cat ControlM_Shutdown.service
# /etc/systemd/system/ControlM_Shutdown.service
[Unit]
Description=Run ControlM shutdown process
Requires=graphical.target multi-user.target network.target network.service 
sockets.target
DefaultDependencies=no
Before=shutdown.target reboot.target halt.target poweroff.target kexec.target

[Service]
Type=oneshot
RemainAfterExit=true
ExecStart=/bin/true
ExecStop=/bin/bash /root/scripts/control-m_shutdown.sh
TimeoutStopSec=4min

[Install]
WantedBy=multi-user.target
[root@kccontrolmt01 ~]#

Re: RHEL 7 (systemd) reboot

[Adrian Klaver]

On 10/11/18 6:33 AM, Bryce Pepper wrote:

Adrian,

Thanks for being willing to dig into this.

You are correct there are other scripts being called from mine (delivered by 
BMC with their software).   In order to stay in support and work with their 
updates I use the vendor supplied scripts/programs.

The Control-M product is installed on this single server and is broken down 
into the following parts:
Enterprise server with dedicated postgresql instance
Distributed datacenter with agent and dedicated postgresql instance
Linux datacenter with with agent and dedicated postgresql instance

To cut down on the noise, my post only focused on the "Distributed" side and 
shutdown process -- although the ControlM_Shutdown.service unit stop script manages all 
of the above components.

In the ControlM_Shutdown.service there is a requires statement identifying that 
 network must be available while this systemd unit runs.

You noticed that the eth0 disconnected in the /var/log/messages.   I showed 
that to highlight that the unit was not executing in the order I had intended, 
again refer to the requires statement.

The second shebang is from one of the invoked subscripts (stop_ctmdist_server.sh) and is the 
"main" shutdown sequence for the Distributed datacenter (I think the "SQL 
server" echo from BMC is because it can be configured with other databases and they use it in 
a generic term --- not meaning sqlserver from Microsoft).

The dbversion check is being used to verify pgsql instance for this datacenter 
is running and returns a non-zero return code if the instance is unreachable (I 
could use pg_isready or pg_ctl but would diverge further from the BMC supported 
technique).

You probably also noticed in the earlier posted shutdown service a requires of 
CTM_Postgre.service.  This was one of my attempts to ensure the instance was 
available by actually starting the instance outside of the BMC routines (if it 
is already running the BMC routines will not start -- the dbversion check is on 
the start side also).  I thought if I managed the postgresql instance outside 
of the product I could ensure it was running.  Unfortunately that didn't work 
as the instance shutdown on its own, presumably a resource (perhaps network) 
was terminated and postgresql shutdown.

So to restate the original post...   It appears the postgresql instance is 
unavailable when the stop script runs.

Thanks,
Bryce

[root@kccontrolmt01 ~]# systemctl --full cat ControlM_Shutdown.service
# /etc/systemd/system/ControlM_Shutdown.service
[Unit]
Description=Run ControlM shutdown process
Requires=graphical.target multi-user.target network.target network.service 
sockets.target
DefaultDependencies=no
Before=shutdown.target reboot.target halt.target poweroff.target kexec.target


Again I am not a systemd expert, but I believe the Before line above is 
the opposite of what you want:


https://serverfault.com/questions/812584/in-systemd-whats-the-difference-between-after-and-requires#812589

Above quotes man 
page(https://www.freedesktop.org/software/systemd/man/systemd.unit.html):


"... Note that when two units with an ordering dependency between them 
are shut down, the inverse of the start-up order is applied. i.e. if a 
unit is configured with After= on another unit, the former is stopped 
before the latter if both are shut down. ..."





[Service]
Type=oneshot
RemainAfterExit=true
ExecStart=/bin/true
ExecStop=/bin/bash /root/scripts/control-m_shutdown.sh
TimeoutStopSec=4min

[Install]
WantedBy=multi-user.target
[root@kccontrolmt01 ~]#




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

Fwd: Want to acquire lock on tables where primary of one table is foreign key on othere

[Abhishek Tripathi]

-- Forwarded message -
From: Abhishek Tripathi 
Date: Thu, Oct 11, 2018 at 3:18 PM
Subject: Want to acquire lock on tables where primary of one table is
foreign key on othere
To: 


Dear Concerned Person,
 I write this email to get the information about locking which I am not
getting from anywhere.
Actually I have acquired a "Select for Update" on a table whose id is
refrence as a foreign key on another table So I want those table won't
update until there is lock. Is it possible? Becuase postgres is acquiring
lock but AccessShare Lock which allow to write on those table How I
restrict this.



Thanks and Regards
Abhishek

RE: RHEL 7 (systemd) reboot

[Bryce Pepper]

Adrian,

I tried changing the Before to After but the postgresql instance was still 
shutdown too early. 

I appreciate all of the help but think I'm going to ask the patching group to 
ensure they stop the control-m services prior to reboot. 

Bryce

Oct 11 09:19:57 kccontrolmt01 su[9816]: pam_unix(su-l:session): session opened 
for user sa_ctmlinux_uat by (uid=0)
Oct 11 09:19:57 kccontrolmt01 systemd[1]: Started Restore /run/initramfs.
Oct 11 09:19:57 kccontrolmt01 stop_ctmdist_agent.sh[9671]: setenv: Too many 
arguments.
Oct 11 09:19:57 kccontrolmt01 stop_ctmlinux_agent.sh[9672]: setenv: Too many 
arguments.
Oct 11 09:19:57 kccontrolmt01 stop_ctmdist_agent.sh[9671]: Killing 
Control-M/Agent Listener pid:5595
Oct 11 09:19:57 kccontrolmt01 stop_ctmlinux_agent.sh[9672]: Killing 
Control-M/Agent Listener pid:5977
Oct 11 09:19:58 kccontrolmt01 stop_ctmdist_agent.sh[9671]: 2018-10-11 09:19:58 
Listener process stopped
Oct 11 09:19:58 kccontrolmt01 stop_ctmlinux_agent.sh[9672]: 2018-10-11 09:19:58 
Listener process stopped
Oct 11 09:19:58 kccontrolmt01 stop_ctmlinux_agent.sh[9672]: Killing 
Control-M/Agent Tracker pid:6199
Oct 11 09:19:58 kccontrolmt01 stop_ctmdist_agent.sh[9671]: Killing 
Control-M/Agent Tracker pid:6172
Oct 11 09:19:58 kccontrolmt01 systemd[1]: Stopped Dynamic System Tuning Daemon.
Oct 11 09:19:59 kccontrolmt01 stop_ctmlinux_agent.sh[9672]: 2018-10-11 09:19:59 
Tracker process stopped
Oct 11 09:19:59 kccontrolmt01 stop_ctmdist_agent.sh[9671]: 2018-10-11 09:19:59 
Tracker process stopped
Oct 11 09:19:59 kccontrolmt01 systemd[1]: Stopped Eracent EUA Service.
Oct 11 09:19:59 kccontrolmt01 su[9815]: pam_unix(su-l:session): session closed 
for user sa_ctmdist_uat
Oct 11 09:19:59 kccontrolmt01 su[9816]: pam_unix(su-l:session): session closed 
for user sa_ctmlinux_uat
Oct 11 09:19:59 kccontrolmt01 systemd[1]: Stopped Control-M CTM Dist Agent.
Oct 11 09:19:59 kccontrolmt01 systemd[1]: Stopping Control-M CTM Dist Server...
Oct 11 09:19:59 kccontrolmt01 systemd[1]: Stopped Control-M CTM Linux Agent.
Oct 11 09:19:59 kccontrolmt01 systemd[1]: Stopping Control-M CTM Linux Server...
Oct 11 09:19:59 kccontrolmt01 su[10319]: (to sa_ctmdist_uat) root on none
Oct 11 09:19:59 kccontrolmt01 su[10320]: (to sa_ctmlinux_uat) root on none
Oct 11 09:19:59 kccontrolmt01 systemd[1]: Requested transaction contradicts 
existing jobs: Transaction is destructive.
Oct 11 09:19:59 kccontrolmt01 systemd-logind[777]: Failed to start session 
scope session-c12.scope: Transaction is destructive.
Oct 11 09:19:59 kccontrolmt01 su[10319]: pam_systemd(su-l:session): Failed to 
create session: Resource deadlock avoided
Oct 11 09:19:59 kccontrolmt01 su[10319]: pam_unix(su-l:session): session opened 
for user sa_ctmdist_uat by (uid=0)
Oct 11 09:19:59 kccontrolmt01 systemd[1]: Requested transaction contradicts 
existing jobs: Transaction is destructive.
Oct 11 09:19:59 kccontrolmt01 systemd-logind[777]: Failed to start session 
scope session-c13.scope: Transaction is destructive.
Oct 11 09:19:59 kccontrolmt01 su[10320]: pam_systemd(su-l:session): Failed to 
create session: Resource deadlock avoided
Oct 11 09:19:59 kccontrolmt01 su[10320]: pam_unix(su-l:session): session opened 
for user sa_ctmlinux_uat by (uid=0)
Oct 11 09:19:59 kccontrolmt01 systemd[1]: Stopped Eracent EPA Service.
Oct 11 09:19:59 kccontrolmt01 systemd[1]: Stopped target Network.
Oct 11 09:19:59 kccontrolmt01 systemd[1]: Stopping Network.
Oct 11 09:19:59 kccontrolmt01 systemd[1]: Stopping LSB: Bring up/down 
networking...
Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: setenv: Too many 
arguments.
Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: Stopping CONTROL-M 
application
Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: SQL Server is not 
running.
Oct 11 09:20:00 kccontrolmt01 stop_ctmlinux_server.sh[10318]: setenv: Too many 
arguments.
Oct 11 09:20:00 kccontrolmt01 stop_ctmlinux_server.sh[10318]: Stopping 
CONTROL-M application
Oct 11 09:20:00 kccontrolmt01 stop_ctmlinux_server.sh[10318]: SQL Server is not 
running.
Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: 

Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: Shutting down 
CONTROL-M.
Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: 

Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: Waiting ...
Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: psql action 
failed. cannot perform sql command in 
/data00/ctmdist/ctm_server/tmp/upd_CMS_SYSP
Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: db_execute_sql 
failed while processing /data00/ctmdist/ctm_server/tmp/upd_CMS_SYSPRM_10512.sq
Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: Failed to update 
CMS_SYSPRM table.
Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: Be aware that the 
Configuration Agent might start the CONTROL-M/Server
Oct 11 09:20:00 kccontrolmt01 stop_ctmlinux_server

Re: Want to acquire lock on tables where primary of one table is foreign key on othere

[Alban Hertroys]

On Thu, 11 Oct 2018 at 16:38, Abhishek Tripathi
 wrote:
> Actually I have acquired a "Select for Update" on a table whose id is 
> refrence as a foreign key on another table So I want those table won't update 
> until there is lock. Is it possible? Becuase postgres is acquiring lock but 
> AccessShare Lock which allow to write on those table How I restrict this.

For what purpose do you want that? What is inadequate about the lock
that Postgres acquires?

Table locks are very rarely what you want, as it blocks all concurrent
access to the entire table, while that is only necessary for a few
rarely used corner cases; a foreign key update is not among those.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

parallel PITR for HA setup

[magodo]

Hello,

I have a HA setup, a standby following a primary.

At beginning, when I do PITR for it, I just recover the primary the
first, then remake the standby by doing a basebackup against primary
and restart the standby with recovery.conf properly.

This works well, however, it takes long time when I have tons of data
to recover. Then I starts to find out how to do parallel PITR for both
clusters and re-establish the relationship between them. Then I find an
issue, after recovery for both clusters, they are at same LSN. Then on
the standby, I need to create a recovery.conf file, and restart it,
which will end up with standby's LSN newer than the current flushed one
on primary.

My solution to this problem is to do a restart also on primary. I
wonder if this is a proper way.

Thank you in advance!
---
Magodo

Re: Advice on logging strategy

[Jeff Janes]

On Thu, Oct 11, 2018 at 6:27 AM Mike Martin  wrote:

> I have a question on logging strategy
>
> I have loggin set to
> log_statement = 'all' on a network database with logging set to csv so I
> can import it to a logging table
>
> However the database is populated via a nightly routine downloading data
> via REST APIusing prepared statements
>
> This results in enormous log files which take ages to import using copy
> becuase each execute statement is logged with the parameters chosen
>
> Is there any way around this?
>

One option is to convert to using COPY...FROM STDIN rather than prepared
INSERTs.

Another is to create a user specifically for bulk population, and do a
'ALTER USER bulk_load SET log_statement=none` to override the global
log_statement setting.

Cheers,

Jeff

Re: RHEL 7 (systemd) reboot

[Adrian Klaver]

On 10/11/18 7:53 AM, Bryce Pepper wrote:

Adrian,

I tried changing the Before to After but the postgresql instance was still 
shutdown too early.


In an earlier post you had:

cat ControlM_Shutdown.service
[Unit]
Description=Run mycommand at shutdown
Requires=network.target CTM_Postgre.service

Did you add CTM_Postgre.service to After= ?

My suspicion being that CTM_Postgre.service is running before you get to 
ControlM_Shutdown.service. Unless of course CTM_Postgre.service does not 
exist anymore.


Then there is this:

Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: setenv: Too 
many arguments.
Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: Stopping 
CONTROL-M application
Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: SQL Server 
is not running.
Oct 11 09:20:00 kccontrolmt01 stop_ctmlinux_server.sh[10318]: setenv: 
Too many arguments.
Oct 11 09:20:00 kccontrolmt01 stop_ctmlinux_server.sh[10318]: Stopping 
CONTROL-M application
Oct 11 09:20:00 kccontrolmt01 stop_ctmlinux_server.sh[10318]: SQL Server 
is not running.


which to me looks like the script is running twice



I appreciate all of the help but think I'm going to ask the patching group to 
ensure they stop the control-m services prior to reboot.


Yeah, there seems to be hidden dependencies happening.


Bryce





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

RE: RHEL 7 (systemd) reboot

[Bryce Pepper]

I disabled and removed the CTM_Postgre.service as it didn't help (and I didn't 
want too many moving parts left out there).

I did find a post 
https://superuser.com/questions/1016827/how-do-i-run-a-script-before-everything-else-on-shutdown-with-systemd
 that I think is getting me closer.

I triedRequiresMountsFor=/data00which starts the script much sooner but 
unfortunately  the  postgresql instance is unreachable by the time the script 
gets there.

These are two unique datacenter shutdowns: ctmdist  & ctmlinux 

Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: setenv: Too many 
arguments.
Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: Stopping CONTROL-M 
application Oct 11 09:20:00 kccontrolmt01 stop_ctmdist_server.sh[10316]: SQL 
Server is not running.
Oct 11 09:20:00 kccontrolmt01 stop_ctmlinux_server.sh[10318]: setenv:
Too many arguments.
Oct 11 09:20:00 kccontrolmt01 stop_ctmlinux_server.sh[10318]: Stopping 
CONTROL-M application Oct 11 09:20:00 kccontrolmt01 
stop_ctmlinux_server.sh[10318]: SQL Server is not running.

Re: how to identify the timeline of specified recovery_target_time when do multiple PITR

[Laurenz Albe]

magodo wrote:
> On Thu, 2018-10-11 at 06:35 +0200, Laurenz Albe wrote:
> > magodo wrote:
> > > OK... Just take another example:
> > > 
> > >  A B
> > > BASE-+-+--o1 (recover to
> > > A)  1
> > >  | |   C
> > >  +.|...+---o2 (regret, recover to
> > > B) 2
> > >|   |
> > >+...|..--o3 (regret again, recover to
> > > C)  3
> > >| 
> > >+--
> > > -- 4
> > > 
> > > 
> > > Suppose I'm at "o3" and want to recover to point "C". Because I want to
> > > recover to the first timeline which covers this time point, it means I
> > > wish to recover to timeline 2.
> > 
> > Ah, I finally understand your question.
> > 
> > You assume tht timeline 1 and 2 have ended (that's how you drew it),
> > and that consequently timeline 3 is the "earliest existing" timeline,
> > so why doesn't PostgreSQL choose that one automatically?
> > 
> > Even though you drew it that way, timeline 1 and 2 have not ended, in
> > a way.  There may be more on them.  How should PostgreSQL know what is
> > the last WAL entry on a certain timeline?  For that, it would have to
> > restore and examine *all* WAL segments on that timeline until that fails.
> > 
> > But that is unreasonable because of the potential amount of time
> > and work involved.  Rather, PostgreSQL has to decide at the point where
> > timeline 2 branches off which one it should follow.
> 
> Thank God I finnally conveied my meaning :)
> 
> For one cluster, if , for example, it recovered to "A" at "o1", then
> the switched WAL(in case of stop-then-recover) or .partial
> corresponding WAL(in case of promote) is the last WAL of the timeline1,
> and it makes no sense to consider timeline1 will continue grow after
> "o1", because it has ended.
> 
> You meantioned: "There may be more on them.".
> 
> Would you please give me an example? I must have missed something
> out...

The timeline is determined before any WAL segments are restored, because
the timeline history (as persisted in the *.history files) determines
which WAL segments will be restored.

You seem to assume that the point A at which recovery ended is the end of the
WAL stream of timeline 1, but you cannot know that:
- it could have been a point-in-time-recovery
- it could be that during the first recovery attempt some WAL archives
  were missing, which caused a premature end of recovery,
  but they are there at the second attempt.

Indeed, your sentence

> if , for example, it recovered to "A" at "o1", then
> the switched WAL(in case of stop-then-recover) or .partial
> corresponding WAL(in case of promote) is the last WAL of the timeline1

seems to contradict your drawing, which has B after A on timeline 1.

Example:
Assume that timeline 1 reaches to 000100150030.
We recover to point A, which is in the middle of 000100150020,
and there branch to timeline 2.
After some time, we decide to recover again, starting from a
checkpoint in 000100150010.
We want to recover to 2018-10-11 12:00:00.

How can you know how many WAL segments there are on timeline 1, and if
there is one that extends past 2018-10-11 12:00:00 or not?

The only way would be to restore and read them all *before* you even
decide which timeline you want to choose for recovery.

That would be forbiddingly expensive, wouldn't it?

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

Re: COPY threads

[Peter J. Holzer]

On 2018-10-10 17:19:50 -0400, Ravi Krishna wrote:
> > On Oct 10, 2018, at 17:18 , Andres Freund  wrote:
> > On October 10, 2018 2:15:19 PM PDT, Ravi Krishna  wrote:
> >> If I have a large file with say 400 million rows, can I first split it
> >> into 10 files of 40 million rows each and then fire up 10 different
> >> COPY sessions , each reading from a split file, but copying into the
> >> same table.  I thought not.  It will be great if we can do this.
> > 
> > Yes, you can.
> > 
> Thank you.  Let me test it and see the benefit. We have a use case for this.

You should of course test this on your own hardware with your own data,
but here are the results of a simple benchmark (import 1 million rows
into a table without indexes via different methods) I ran a few weeks
ago on one of our servers:

https://github.com/hjp/dbbench/blob/master/import_pg_comparison/results/claudrin.2018-09-22/results.png

y axis is rows per second. x axis are different runs, sorted from
slowest to fastest (so 2 is the median).

As you can see it doesn't parallelize perfectly: 2 copy processes are
only about 50 % faster than 1, and 4 are about 33 % faster than 2. But
there is a still quite a respectable performance boost.

hp

PS: The script is of course in the same repo, but I didn't include the
test data because I don't think I'm allowed to include that.

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

something weird happened - can select by column value although column value exist

[Dmitry O Litvintsev]

Hi, 

Today the following happened:

Found this error in my production log:

< 2018-10-11 13:31:52.587 CDT >ERROR:  insert or update on table "file" 
violates foreign key constraint "$1"
< 2018-10-11 13:31:52.587 CDT >DETAIL:  Key (volume)=(155303) is not present in 
table "volume".
< 2018-10-11 13:31:52.587 CDT >STATEMENT:  
INSERT INTO file 
(sanity_crc,uid,pnfs_id,crc,deleted,cache_mod_time,drive,volume,sanity_size,cache_status,gid,location_cookie,cache_location,original_library,bfid,pnfs_path,size)
 VALUES (
4002229874,0,'',256655919,'u','2018-10-11 13:31:52','',(SELECT 
id FROM volume where label='A'),65536,'B',0,'C','D','E','F','',197243) 
RETURNING *

file table references volume table on file.volume = volume.id and file.volume 
is FK to volume.id. I doctored the query for privacy replacing string values 
with 'A', 'B'. ... 

(Queries similar to the above quoted  are executed by an application and run 
thousand of times every day for years) 

So, the problem:

SELECT id FROM volume where label='A';
   id   

 155303
(1 row)

BUT:

select * from volume where id = 155303;
 ...
(0 rows)

?!

id is a sequence:

id| integer | not null default 
nextval(('volume_seq'::text)::regclass)


This entry id = 155303 has existed for some time and has a lot of existing file 
entries holding 
FK reference to volume id = 155303

I "fixed" the issue just by:

update volume set id  = 155303 where label='A';

BUT It did not work  right away. Meaning I did this once:

update volume set id  = 155303 where label='A';

no effect. 

I did it again, I also did it;

update volume set id = (select id from volume where  label='A');

and then again 

update volume set id  = 155303 where label='A';

eventually it worked. Now, 


select count(*) from volume where label='A';
 count 
---
 1
(1 row)


What is this? Version 9.3.9,. running on Linux RH6.

Thanks,
Dmitry

Re: COPY threads

[Thomas Kellerer]

Rob Sargent schrieb am 10.10.2018 um 00:45:> Can anyone here tell me whether or 
not the CopyManager facility in

JDBC via org.postgresql:postgresql:42.1.4 is internally
multithreaded? Running on CentOS 7 (all participants), java8,
postgres 10.5


An alternative to creating your own multi-threaded importer, might be to use 
pgLoader which supports that out of the box:

https://pgloader.readthedocs.io/en/latest/pgloader.html#a-note-about-parallelism

I have never used it though, but heard good things about it.

Re: something weird happened - can select by column value although column value exist

[Ron]

On 10/11/2018 03:17 PM, Dmitry O Litvintsev wrote:

Hi,

Today the following happened:

Found this error in my production log:

< 2018-10-11 13:31:52.587 CDT >ERROR:  insert or update on table "file" violates foreign 
key constraint "$1"
< 2018-10-11 13:31:52.587 CDT >DETAIL:  Key (volume)=(155303) is not present in table 
"volume".
< 2018-10-11 13:31:52.587 CDT >STATEMENT:
 INSERT INTO file 
(sanity_crc,uid,pnfs_id,crc,deleted,cache_mod_time,drive,volume,sanity_size,cache_status,gid,location_cookie,cache_location,original_library,bfid,pnfs_path,size)
 VALUES (
 4002229874,0,'',256655919,'u','2018-10-11 13:31:52','',(SELECT 
id FROM volume where label='A'),65536,'B',0,'C','D','E','F','',197243) 
RETURNING *

file table references volume table on file.volume = volume.id and file.volume 
is FK to volume.id. I doctored the query for privacy replacing string values 
with 'A', 'B'. ...

(Queries similar to the above quoted  are executed by an application and run 
thousand of times every day for years)

So, the problem:

SELECT id FROM volume where label='A';
id

  155303
(1 row)

BUT:

select * from volume where id = 155303;
  ...
(0 rows)

?!

id is a sequence:

id| integer | not null default 
nextval(('volume_seq'::text)::regclass)


This entry id = 155303 has existed for some time and has a lot of existing file 
entries holding
FK reference to volume id = 155303

I "fixed" the issue just by:

update volume set id  = 155303 where label='A';

BUT It did not work  right away. Meaning I did this once:

update volume set id  = 155303 where label='A';

no effect.

I did it again, I also did it;

update volume set id = (select id from volume where  label='A');

and then again

update volume set id  = 155303 where label='A';

eventually it worked. Now,


select count(*) from volume where label='A';
  count
---
  1
(1 row)


What is this? Version 9.3.9,. running on Linux RH6.


Index corruption?  Maybe rebuild the FK.


--
Angular momentum makes the world go 'round.

Re: something weird happened - can select by column value although column value exist

[Tom Lane]

Ron  writes:
> On 10/11/2018 03:17 PM, Dmitry O Litvintsev wrote:
>> Today the following happened:
>> < 2018-10-11 13:31:52.587 CDT >ERROR:  insert or update on table "file" 
>> violates foreign key constraint "$1"
>> < 2018-10-11 13:31:52.587 CDT >DETAIL:  Key (volume)=(155303) is not present 
>> in table "volume".
>> [ but it is present ]
>>
>> What is this? Version 9.3.9,. running on Linux RH6.

> Index corruption?  Maybe rebuild the FK.

Yeah, I'd suspect index corruption as well, particularly seeing that this
is a three-year-old point release in a branch that had more than its share
of data corruption issues.  My suggestion is first update to 9.3.latest,
then reindex.

BTW, 9.3.x will be EOL next month, so you should be thinking about getting
onto a later release branch ...

regards, tom lane

Re: COPY threads

[Ravi Krishna]

>>> 
>> Thank you.  Let me test it and see the benefit. We have a use case for this.
> 

Well the result is not what I expected.

this is the sql I used

copy TEST.TABLE1 from '/var/lib/postgresql/data/rkrishna/copytesting/xaa' with 
delimiter '|' NULL as '' CSV HEADER;

From another session

copy TEST.TABLE1 from '/var/lib/postgresql/data/rkrishna/copytesting/xab' with 
delimiter '|' NULL as '' CSV HEADER;

Each had 16M rows.

I see that one copy is blocking other.

The table has no indexes while loading.

Aren't they suppose to run  concurrently without locking ?

Re: COPY threads

[Ravi Krishna]

> 
> Well the result is not what I expected.
> 
> this is the sql I used
> 
> copy TEST.TABLE1 from '/var/lib/postgresql/data/rkrishna/copytesting/xaa' 
> with delimiter '|' NULL as '' CSV HEADER;
> 
> From another session
> 
> copy TEST.TABLE1 from '/var/lib/postgresql/data/rkrishna/copytesting/xab' 
> with delimiter '|' NULL as '' CSV HEADER;
> 
> Each had 16M rows.
> 
> I see that one copy is blocking other.
> 
> The table has no indexes while loading.
> 
> Aren't they suppose to run  concurrently without locking ?
> 
> 

Strike that.  The lock wait is bogus.  The data did get loaded concurrently.

no queryId in post_parse_analyze hook when row is locked

[legrand legrand]

Hello,

When playing with extension pgsentinel,
it seems that post_parse_analyze hook doesn't return query->queryId
(initialized by a similar hook in pg_stat_statements)
when current row is locked.

Is that possible ?

More details found at
https://github.com/pgsentinel/pgsentinel/issues/19

Thanks in advance
Regards
PAscal



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

Re: Code of Conduct plan

[Bruce Momjian]

On Thu, Sep 20, 2018 at 07:12:22AM +0200, Chris Travers wrote:
> If we have a committer who loudly and proudly goes to neo-nazi rallies or
> pickup artist / pro-rape meetups, then actually yes, I have a problem with
> that. That impacts my ability to work in the community, impacts everyone's
> ability to recruit people to work on Postgres, potentially makes people
> reluctant to engage with the community, etc.
> 
> There's a problem here though. Generally in Europe, one would not be able to
> fire a person or even discriminate against him for such activity.  So if you
> kick someone out of the PostgreSQL community for doing such things in, say,
> Germany but their employer cannot fire them for the same, then you have a real
> problem if improving PostgreSQL is the basis of their employment.    EU
> antidiscrimination law includes political views and other opinions so
> internationally that line is actually very hard to push in an international
> project.  So I think you'd have a problem where such enforcement might 
> actually
> lead to legal action by the employer, or the individual kicked out, or both.

Yes, I had the same reaction.  Activity not involving other Postgres
members seems like it would not be covered by the CoC, except for
"behavior that may bring the PostgreSQL project into disrepute", which
seems like a stretch.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: how to identify the timeline of specified recovery_target_time when do multiple PITR

[Laurenz Albe]

magodo wrote:
> > How can you know how many WAL segments there are on timeline 1, and if
> > there is one that extends past 2018-10-11 12:00:00 or not?
> 
> This is the exact problem I want to figure out. My approach is as you said,
> I will parse each archived WAL segment via `pg_xlogdump -r Transaction`,
> and try to find the first least earliest WAL against the specified time.
> This is a linear search, which has complexity of O(n).

That means that the time spent grows linearly, but it is still a lot of time
if there are a lot of WAL archives.

> So if you want to recover to that point of time, how do you choose the 
> timeline?

PostgreSQL doesn't provide auch a functionality, and I can think of three 
reasons:
1) the high cost as mentioned above
2) during archive recovery, it does not happen very often that you have to
   choose between timelines at all.  It is more typical for streaming 
replication.
3) nobody saw a use case for such a functionality

If you really need this functionality, you'll have to write it yourself.

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

Re: Want to acquire lock on tables where primary of one table is foreign key on othere

[Alban Hertroys]

You forgot to CC the list. Also, top-posting is generally not appreciated here.

> On Thu, Oct 11, 2018 at 8:26 PM Alban Hertroys  wrote:
> On Thu, 11 Oct 2018 at 16:38, Abhishek Tripathi
>  wrote:
> > Actually I have acquired a "Select for Update" on a table whose id is 
> > refrence as a foreign key on another table So I want those table won't 
> > update until there is lock. Is it possible? Becuase postgres is acquiring 
> > lock but AccessShare Lock which allow to write on those table How I 
> > restrict this.
> 
> For what purpose do you want that? What is inadequate about the lock
> that Postgres acquires?
> 
> Table locks are very rarely what you want, as it blocks all concurrent
> access to the entire table, while that is only necessary for a few
> rarely used corner cases; a foreign key update is not among those.

> On Fri, Oct 12, 2018 at 10:33 AM Abhishek Tripathi 
>  wrote:
> Thank you for your response. 
> 
> I am explaining my situation there is table A on which I have taken a row 
> lock and the primary key of table A is related with table B as a foreign key 
> so automatically I can not insert new row with that  foreign key  now the 
> primary key of table B is used as foreign key in table C and insertion can be 
> done on table C. I just want to lock table C also No insertion can be done on 
> table C related to table B primary key.
> 

> On 12 Oct 2018, at 8:08, Abhishek Tripathi  wrote:
> 
> My bad sorry actually there is updation-
> 
> there is table A on which I have taken a row lock and the primary key of 
> table A is related with table B as a foreign key so automatically I can not 
> insert new row with that  foreign key on table B that is fine now  table C 
> any  insertion can be done on table C. I just want to lock table C also No 
> insertion can be done on table C becuse table C primary key is related to 
> table B as a foreign key of B.


So your tables (simplified) are something like this?:
create table A (id primary key)
create table B (id primary key, a_id references A (id))
create table C (id primary key, b_id references B (id))

And you insert a new value for b_id in C? That's not possible, unless that 
value exists in table B, which in turn can't exist unless it's a_id exists in 
table A.
That's how foreign key constraints are designed, you don't need to do manual 
locking for that to work.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.