Re: [GENERAL] pgaduit - is there a way to audit a role

[David Steele]

On 10/30/17 6:48 PM, rakeshkumar464 wrote:
> Is there a way to audit a group like as follows
> 
> alter role db_rw set pgaudit.log = 'read,write,function,ddl'  
> 
> and then any user part of db_rw role can be audited automatically.  It does
> not seem to work if I connect to the db as rakesh who is part of db_rw role.

This will not work because settings (GUCs) on a role are not inherited
by roles (or users) that are members of that role.  This is a
characteristic of the roles system and not inherent to pgAudit.

-- 
-David
da...@pgmasters.net


-- 
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] VM-Ware Backup of VM safe?

[David Steele]

On 9/20/17 7:36 AM, PT wrote:
> On Wed, 20 Sep 2017 10:03:15 +0200
> Thomas Güttler  wrote:
> 
>> We run a PostgreSQL 9.6 server in a virtual machine.
>>
>> The virtual machine is managed by the customer.
>>
>> He does backup the VM.
>>
>> Is this enough, is this safe?
> 
> There are so many variables involved with doing that ... I don't think
> anyone can reliably answer that question.
> 
> I recommend you put together a periodic test schedule where you restore
> a machine from the backup and ensure everything works. To be honest, you
> should be doing that anyway.

Restore testing is as must, but a bad backup scheme can result in subtle
errors that are very hard to detect.

If you can't find specific documentation that your VM backup solution is
safe to use with a DBMS then it is almost certainly not safe.  Even if
it says it is there are potential gotchas.  For example, the backup may
not be consistent if you are using multiple volumes.

In addition, data loss on restore will be greater if there is no WAL
archive to play forward from.

-- 
-David
da...@pgmasters.net


-- 
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] Checking data checksums...

[David Steele]

On 12/16/16 5:07 AM, ma...@kset.org wrote:

> I enabled data checksums (initdb --data-checksums) on a new instance and
> was wandering is there a command in the psql console, or from the linux
> console, to force a checksum check on the entire cluster and get error
> reports if it finds some corrupted pages.

The newest release of pgBackRest (http://www.pgbackrest.org) will
validate all data checksums whenever doing a backup.  Invalid checksums
are reported in the backup log and recorded in the backup manifest.

The functionality requires a companion library that has not made it into
the community Debian/RHEL packages yet, but should be available soon.

-- 
-David
da...@pgmasters.net


-- 
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] pgAudit_Analyze - parse error in pgaudit_analyze.log

[David Steele]

On 12/14/16 10:06 PM, Dylan Luong wrote:
> 
> /Can't call method "parse" on an undefined value at ./pgaudit_analyze
> line 509, <$hFile> line 282./
> 
> /at ./pgaudit_analyze line 44/

This looks like the issue that appears to be fixed by this pull request:

https://github.com/pgaudit/pgaudit_analyze/pull/1

I haven't had time to assess to performance impact of this patch yet,
which is why it hasn't been merged.  You are welcome to give it a try.

-- 
-David
da...@pgmasters.net


-- 
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] Wal files - Question | Postgres 9.2

[David Steele]

On 11/24/16 8:05 PM, Patrick B wrote:

> hmm.. I really don't get it.
> 
> 
>  
> If I get messages like: 
> 
> *cp: cannot stat '/walfiles/00021AF800A5': No such file or
> director*y
> 
> In my head, it's saying that it was unable to recover that file and,
> because of that, there is missing data.
> Even if the server is able to connect to tbe master via streaming
> replication, there might be missing data. There might be data that is
> into master but not into slave.
> 
> Am I wrong? If so, I don't understand why are the wal_files for, then.

This is normal as far as it goes.  Postgres keeps checking for the WAL
file that it needs to become consistent and cp keeps throwing errors
when it can't find the file (yet).

This simply demonstrates how unsuitable cp is as a recovery command.  I
recommend you look at doing backup/archive with a professional tool such
as pgBackRest (http://www.pgbackrest.org) or Barman
(http://www.pgbarman.org/).

-- 
-David
da...@pgmasters.net


-- 
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] What is the best thing to do with PUBLIC schema in Postgresql database

[David Steele]

On 11/4/16 3:58 PM, Hu, Patricia wrote:

Since it could potentially be a security loop hole. So far the action taken to 
address it falls into these two categories:

drop the PUBLIC schema altogether. One of the concerns is with some of the 
system objects that have been exposed through PUBLIC schema previously, now 
they will need other explicit grants to be accessible to users. e.g 
pg_stat_statements.
keep the PUBLIC schema but revoke all privileges to it from public role, 
then grant as necessity comes up.

Any feedback and lessons from those who have implemented this?


I always drop the public schema as the first step of any build and have 
never seen any ill effects.


Nothing is exposed by default in the public schema unless you install 
extensions into it.


--
-David
da...@pgmasters.net


--
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] WAL segmentes names in wrong order?

[David Steele]

On 11/3/16 1:16 PM, Tom DalPozzo wrote:


so if I understand right, the ...DE file's previous name, was less than
...C6, then it was renamed in big advance for later use. I was missing
this advance.


That is correct.

--
-David
da...@pgmasters.net


--
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] WAL segmentes names in wrong order?

[David Steele]

On 11/3/16 12:28 PM, Tom DalPozzo wrote:

Hi,
I found, in pg_xlog dir, several WAL segment files with old modification
timestamp but with their names greater than more recent files.
Ex.:
000100C6  modified today
000100DE  modified yesterday


This is completely normal.  WAL files are recycled so a file with a 
later name can have an earlier timestamp.  What this means is it is 
available but has not been used to record transactions yet.


So, 000100C6 is the end of your current WAL stream.

--
-David
da...@pgmasters.net


--
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] restore a specific schema from physical backup

[David Steele]

On 7/29/16 5:31 PM, Rakesh Kumar wrote:
>> Are you saying that?:
>>
>> 1) You ran pg_basebackup against a live cluster and sent the output to
>> another location.
>>
>> 2) At the other location the cluster is not in use.
>>
>> 3) You want to grab the contents of the inactive cluster directly off the
>> disk.
>>
>> If that is the case, then no it is not possible without making the cluster
>> live.
>>
>> If you mean something else then more details are needed.
> 
> Sure.
> 
> 1 - You ran pg_basebackup on node-1 against a live cluster and store
> it on NFS or tape.
> 2 - Do a  restore on node-2 from the backup taken on (1), but only for
> a subset of the database
>  (schema/database)
> 3- Put the cluster live on node-2 after (2) completes. Essentially the
> cluster will now be a small
> subset of cluster on node-1.
> 
> Benefit: If I have to restore only 5% of entire db, it should be lot faster.

pgBackRest allows specified databases to be restored from a cluster backup:

http://www.pgbackrest.org/user-guide.html#restore/option-db-include

I know you are interested in schema-level restores but this is the
closest thing that I know of.

-- 
-David
da...@pgmasters.net


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

Re: [HACKERS] [GENERAL] Request - repeat value of \pset title during \watch interations

[David Steele]

On 3/17/16 7:00 PM, Tom Lane wrote:
> David Steele  writes:
>> On 3/17/16 5:07 PM, David G. Johnston wrote:
>>> Figured out it had to be added to 2016-09...done
> 
>> Hmm ... this patch is currently marked "needs review" in CF 2016-03.  Am
>> I missing something, should this have been closed?
> 
> The message I saw was post-1-March.  If it was in fact submitted in
> time for 2016-03, then we owe it a review.

I meant to add the CF record and forgot:

https://commitfest.postgresql.org/9/480

It was added 2016-01-13 by Michael Paquier.

-- 
-David
da...@pgmasters.net


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

Re: [HACKERS] [GENERAL] Request - repeat value of \pset title during \watch interations

[David Steele]

On 3/17/16 5:07 PM, David G. Johnston wrote:

> Figured out it had to be added to 2016-09...done

Hmm ... this patch is currently marked "needs review" in CF 2016-03.  Am
I missing something, should this have been closed?

-- 
-David
da...@pgmasters.net


-- 
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] Hot standby and xlog on a ramdisk

[David Steele]

On 2/4/16 9:46 AM, Tore Halvorsen wrote:
> On Thu, Feb 4, 2016 at 3:30 PM, Scott R Mead  > wrote:
> 
>> > Can a hot standby run correctly with the xlog-files on a
>> ramdisk?
>> >
>>
>> Yes, but, if you lose any, you'll need to rebuild the standby.
>>
>>
>> Even if the master and archive contain all the wal files?
>>
> If you have the file somewhere, you're okay, whether it's on the
> master, standby or an archive host.  If you're using log-shipping,
> you'll need all the WALs.
> 
> 
> So, just to verify my understanding. Using a ramdisk for pg_xlog on a
> hot standby slave should be ok in all cases as long as the archive and
> master retains all wal files that are not applied?

Yes.  If the pg_xlog ramdisk is lost on the standby then Postgres will
start throwing errors but there will be no corruption of the heap.  It's
possible that Postgres will terminate at this point, but after
remounting the ramdisk you can restart Postgres on the standby and
everything will be fine.

If something goes really horribly wrong you can always rebuild the standby.

-- 
-David
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Offline Tablespaces and Partial Restore

[David Steele]

On 1/12/16 6:02 AM, Pedro França wrote:
> Yea, we are considering doing all the function calls and access to
> tables with dblink and see how bad this would hurt performance. It will
> be tough to explain this to the SQL Server guys.

You should consider using the Postgres FDW
(http://www.postgresql.org/docs/current/static/postgres-fdw.html).  It's
a far more elegant solution to the problem of distributed data.

> Maybe there is another approach to deal with this kind of environment?
> Does Postgres only relies on replication for high availability? Maybe an
> extension that I don't know about?

There are a number of logical replication tools (Slony, Bucardo, BDR,
etc.) but I don't see how they would help in this case (because of your
restore requirements).


-- 
-David
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Offline Tablespaces and Partial Restore

[David Steele]

On 1/11/16 8:41 AM, Pedro França wrote:

I've tried to put the data on another database but it didn't help as
tablespaces are required cluster-wide on startup.


If putting the data in a separate database works for you then it 
wouldn't be a big leap to put it in a separate cluster. 
Functionality-wise it's pretty similar since you can't join across 
databases in Postgres.


There are some advantages to this design since you can separate the 
buffer caches, tune optimizer variables, wal settings, etc.


--
-David
da...@pgmasters.net


--
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] - PostgreSQL Replication Types

[David Steele]

Hi Will,

On 12/17/15 10:17 AM, Will McCormick wrote:
> I inherited a 9.1 replication environment
> 
> Few basic questions that I can't find clear answers / clarifications for
> if possible:
> 
> 3 types of replication in 9.1 I've read about from the offical docs:
> 
> 1) warm standby 

This is a standby which is applying WAL segments from the master (via
recovery_command (log shipping) or streaming replication.

> 2) hot standby

Just like a warm standby but adds the ability to do read-only queries
(and the master must know it is a hot standby).

> 3) streaming replication

Another way to deliver WAL segments from the master, but the advantage
over recovery_command log shipping is that transactions are applied as
soon as they happen on the master, rather than when a WAL segment is
pushed from the master to the archive.

> I'm using streaming replication I believe,  the only indication I have
> is that  there is the primary_conninfo on the standby. Is this the only
> indication?
> 
> Is it possible to get if using streaming replication under normal
> operations?
> 
> /cp: cannot stat `/opt/postgres/9.1/archive/0007000F0057':
> No such file or directory/
> 
> /cp: cannot stat `/opt/postgres/9.1/archive/0007000F0057':
> No such file or directory/
> 
> /LOG:  streaming replication successfully connected to primary/
> 
> /FATAL:  could not receive data from WAL stream: FATAL:  requested WAL
> segment 0007000F0057 has already been removed/

What this means is your stannby has been out-of-date for some time.  The
WAL segment it needs has been expired from both the archive
(/opt/postgres/9.1/archive) and the master's pg_xlog directory.

Your only option now (unless you can find to required WAL segments
somewhere) is to do a new backup using pg_basebackup or some other
mechanism to bring the standby up to date.

> My understanding is that warm standby and hot standby do log shipping
> and there is a greater window for transactions not to be send to the
> standby because WAL XLOG must be filled.

A hot or warm standby can be maintained with either log shipping or
streaming replication.

> Whereas Streaming replication basically sends at the transaction level?

But yes, this is the advantage of streaming replication.

I have written a tutorial that covers setting up a hot standby with or
without streaming replication using pgBackRest:

http://www.pgbackrest.org/user-guide.html#replication

You can replace backup/restore/archive with other methods but the
principal remains the same.  The tutorial is for 9.4 but should work
equally well with 9.1.

-- 
-David
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] postgresql 9.3 failover time

[David Steele]

On 12/12/15 2:08 PM, Shay Cohavi wrote:
> *I have postgresql 9.3 setup with 2 nodes (active/standby with streaming
> replication & continuos archiving).*
> *I have created 2 failover & failback script in order to perform a
> switchover between the DB servers:*
> *1. failover - create a trigger file in order to promote the new primary.*
> *2. failback - perform a base backup as mentions in :*
> *   a. start backup on the primary.*
> *   b. stop the failed node .*
> *   *didn't delete the DB directory on the failed node
> *   c. performing rsync between the nodes.*

If you use rsync here be sure to use checksums.  The clusters are very
similar to each other and rsync timestamp resolution could become a problem.

> *   d.stopping the backup on the primary.*
> *   e.performing rsync on the pg_xlog.*
> *   f. creating a recovery.conf*
> /
> /standby_mode = 'on'/
> /primary_conninfo = 'host=10.50.1.153 port=5432 user=usr password=pass'/
> /restore_command = 'scp 10.50.1.153:/home/postgres/archive/%f %p'/
> /trigger_file = '/home/postgres/databases/fabrix/trigger'/
> /archive_cleanup_command = 'ssh 10.50.1.153
> /home/postgres/pg_utils/archive_cleanup.sh %r'/
> *
> *   g. starting the failed node as secondary.*
> *
> *the switchover method:*
> *1. stop the primary node.*
> *2. promote the secondary node (failover.sh).*
> *3. perform failback on the failed node.*
> *4. start the failed node.*
> *
> *this method works great! *
> *
> *but if I perform multiple switchovers (>20), each time the new primary
> gets promoted (trigger file) - it takes longer because it searches the
> timelines on the archive. *

This is an indication that your backup/restore process is not working
correctly.  Postgres should only look for timelines that are greater
than the current timeline.

> *for example:*
> 
> /[2015-12-12 20:35:10.769 IST] LOG:  trigger file found:
> /home/postgres/databases/fabrix/trigger/
> /[2015-12-12 20:35:10.769 IST] FATAL:  terminating walreceiver process
> due to administrator command/
> /scp: /home/postgres/archive/0094000200DC: No such file or
> directory/
> /[2015-12-12 20:35:10.893 IST] LOG:  record with zero length at 2/DC000168/
> /[2015-12-12 20:35:10.893 IST] LOG:  redo done at 2/DC000100/
> /scp: /home/postgres/archive/0094000200DC: No such file or
> directory/
> /scp: /home/postgres/archive/0093000200DC: No such file or
> directory/
> /scp: /home/postgres/archive/0092000200DC: No such file or
> directory/
> /./
> /./
> /./
> /
> /
> /scp: /home/postgres/archive/0091000200DC: No such file or
> directory/
> /scp: /home/postgres/archive/009200DC: No such file or
> directory/
> /scp: /home/postgres/archive/0095.history: No such file or directory/
> /[2015-12-12 20:35:11.801 IST] LOG:  selected new timeline ID: 149/
> /[2015-12-12 20:35:11.931 IST] LOG:  restored log file
> "0094.history" from archive/
> /[2015-12-12 20:35:12.173 IST] LOG:  archive recovery complete/
> /[2015-12-12 20:35:12.181 IST] LOG:  database system is ready to accept
> connections/
> /[2015-12-12 20:35:12.181 IST] LOG:  autovacuum launcher started/

It's not clear to me how you got to timeline 149.  Some lines have been
removed - did the history log requests go all the way to 148?

Rsync is possibly your issue here - maybe pg_control is not being copied
because the timestamp is the same on both systems (rsync only has a 1
second time resolution so this is very possible between a master and a
streaming replica).  Try rsync with checksums (--checksum) and see if
that makes a difference.

-- 
-David
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Complete Recovery 9.4.4

[David Steele]

On 12/11/15 3:55 PM, Will McCormick wrote:
> Basic backup and recovery question. I want to perform complete restore
> and recovery using continuous archive mode.
> 
> Lets imagine we have a single table MYTABLE. Here are my high level steps
> 
> 1) Add a record  A) to MYTABLE
> 2) Take a file system backup to be used for recovery. This backup
> includes archive logs

How are you taking the backup?  Are you using cp for archiving?

> 3) Add a record B) to MYTABLE
> 
> Timeline -> incident happens need backup
> 
> 4) Stop PG
> 5) Make copy of current state including PGDATA w/ pg_xlog and WAL archives
> 6) Cleanup PGDATA /wpg_xlog and WAL archive  directory
> 7) Restore backup taken in step 2, placing contents in PGDATA /w pg_xlog
> and archives

Don't restore the contents of pg_xlog.

> 8) Remove contents of pg_xlog
> 9) Copy contents of pg_xlog in step 5 to PGDATA/pg_xlog

Don't do this - allow Postgres to get the archive logs it needs using
the recovery_command.

> 10) Create recovery.conf file with cp
> /database/postgres/product/9.4.4/archive/%f %p
> 11) Startup the server
> 
> What I see happen is 1) restores but my change in step 3) is not.

There's not enough detail here to determine where you are going wrong.
It could be something in step #2 or steps #5-10.

You should have a look at pgBackRest - it's a complete backup and
recovery solution that takes care of all the dirty work for you:

http://www.pgbackrest.org/user-guide.html

And allows you to do Point-in-Time Recovery with a single command:

http://www.pgbackrest.org/user-guide.html#pitr

This a detailed guide that shows you exactly how PITR works and how to
verify your result.  Even if you don't use pgBackRest it may be useful
for you to read it.

-- 
-David
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Pgbasebackup help

[David Steele]

On 12/3/15 12:59 AM, Yelai, Ramkumar IN BLR STS wrote:

What I wanted to achieve is simple copy of Data folder. I can't shutdown the 
database during the backup and unable to use file system copy of data folder as 
it creates inconsistency and don't want to use pg_dump.

Hence I decided to use Pg_basebackup for copying the base backup and don't want 
to replay the wal.


Replaying WAL is *not* optional.  Each restore will have to replay at 
least one WAL segment to become consistent, depending on write volume 
during the backup.



Anyway, pg_basebackup put checkpoint before copying the data folder. For me it 
is enough to restore till checkpoint.


This won't work - the database keeps running and making changes after 
the checkpoint.



I saw this link 
http://blog.veritech.io/2014/10/automated-backup-for-postgresql-cluster.html.

In this link also, I have not seen they have enabled archive_mode. Archive mode 
is not necessary as long as you streaming the your wal files to pg_xlog.


These instructions are for bringing up a replica.  Even if this is OK 
for your purposes, it still would not get you a database at time T3. 
You are supposing that because this method does not use archiving that



Also, even if I have all wal files , how do I  restore till time T3. I am 
analyzing at pgbackrest to know how to restore backup till time T3.


To restore to time T3 you would select a backup that ended *before* T3 
then using point-in-time recovery to play forward to T3.


That should be explained pretty clearly in the user guide - if there's 
something you don't understand then it would be helpful to know so I can 
improve the guide.


--
-David
da...@pgmasters.net


--
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] Pgbasebackup help

[David Steele]

On 12/2/15 6:25 PM, Jim Nasby wrote:

On 12/2/15 1:56 PM, David Steele wrote:

>Also, I don’t want enable archive_mode = on as it needs to maintain
>archives files.

As it turns out, archiving would be the solution to your problem.  If
you were archiving you could restore a*previous*  backup and then replay
WAL to exactly T3.  There might be some jitter from clock differences
but it should have the desired effect.


And in this case previous could be a PG backup taken immediately before
the backup of the private DBMS.


Exactly.


I don't remember off-hand if pg_basebackup has an option for delaying
the pg_stop_backup() call, but if it does then I think the simplest
thing is to just delay that until after your private DBMS backup is
complete.


There's no option to delay it in pg_basebackup but it can be called 
whenever you like when using pg_start/stop_backup.  So, yeah, if the 
postgres backup were started first then pg_stop_backup() could be called 
right after the private backup stops.  That would make them pretty much 
in sync.


Of course, it's a lot of work to get that going.  pg_basebackup does a 
lot of work for you.


--
-David
da...@pgmasters.net


--
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] Pgbasebackup help

[David Steele]

On 11/30/15 6:28 AM, Yelai, Ramkumar IN BLR STS wrote:
> Hi All,
>  
> I need some help in postgresql base backup.
>  
> We are currently using multiple DBMS in our project and postgresql is
> one of them. Our private DBMS keeps the online data and postgresql keeps
> online as well as historical data.
>  
> At present, we are doing the backup/restore process for our project. So
> we planned to use Pg_basebackup instead of  pg_dump. 
>  
> Below is the backup steps.
>  
> Time-T1   = Start the backup of private DBMS.
> Time-T2   = Finished the private backup DBMS.
> Time-T3   = Start the pg_basebackup.
> Time-T4   = End the Pg_basebackup.
>  
> Here the requirement is we don’t want to restore the data after
> Time-T3.  But when I followed this approach
> _https://opensourcedbms.com/dbms/point-in-time-recovery-pitr-using-pg_basebackup-with-postgresql-9-2/_,
> I am still getting the information’s archived from Time-T3 to TimeT4.
>  
> Seems, WAL archives are holding all the transactions, which are happened
> between Time T3 – Time T4.

This is the expected behavior.  The WAL generated during the backup must
be replayed to make the database consistent so T4 is the earliest you
can possibly stop recovery.

> Also, I don’t want enable archive_mode = on as it needs to maintain
> archives files.

As it turns out, archiving would be the solution to your problem.  If
you were archiving you could restore a *previous* backup and then replay
WAL to exactly T3.  There might be some jitter from clock differences
but it should have the desired effect.

I've done this to have development database reasonably in sync with each
other and in practice it works quite well.

> So I decided the enable only these parameters.
>  
> Postgresql.conf
> -
> wal_level = hot_standby
> max_wal_senders = 1
>  
> And added replication permissions for the current user in pg_hba.conf.
>  
> It does, what I need it. In the backup I did not have the data between
> T3-T4.

There's not not enough detail here for me to make out what you are
doing.  Is there still a pg_basebackup going on or are you just copying
files?

If archive_mode is not enabled then wal_level = hot_standby is likely
ignored.

What you end up with may start, but I doubt it's consistent.  I don't
see how you could use pg_basebackup without archiving and end up at T3
with a consistent cluster.

Here's a tutorial I wrote for pgBackRest that covers point in time
recovery and goes into a bit more detail than the article you cited:

http://www.pgbackrest.org/user-guide.html#pitr

This method requires archive_mode to be enabled, which I believe is the
correct way to achieve the desired result.

-- 
-David
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] psql --quiet

[David Steele]

On 10/27/15 3:32 PM, David Steele wrote:

On 10/27/15 3:16 PM, Leo Baltus wrote:

postgresql-9.4.5

When running pg_stop_backup() psql outputs on stderr:

NOTICE:  pg_stop_backup complete, all required WAL segments have been
archived

--quiet does not seem to suppress it, what does?


This should work:

set client_min_messages='warning';

This must be done in the same session as the pg_start_backup().


Ugh, pg_stop_backup() rather.

--
-David
da...@pgmasters.net


--
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] psql --quiet

[David Steele]

On 10/27/15 3:16 PM, Leo Baltus wrote:

postgresql-9.4.5

When running pg_stop_backup() psql outputs on stderr:

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

--quiet does not seem to suppress it, what does?


This should work:

set client_min_messages='warning';

This must be done in the same session as the pg_start_backup().

--
-David
da...@pgmasters.net


--
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] Use tar to online backup has an error

[David Steele]

On 9/24/15 10:15 AM, Stephen Frost wrote:

* David Steele (da...@pgmasters.net) wrote:

It's actually perfectly normal for files to disappear during a
backup, even when pg_start_backup() is called first (never perform
file-level backup with calling pg_start_backup()).  The database


*without* calling pg_start_backup, you mean. :)


Whoops!  That's definitely what I meant.

--
-David
da...@pgmasters.net


--
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] Use tar to online backup has an error

[David Steele]

On 9/24/15 9:29 AM, Yves Dorfsman wrote:

On 2015-09-24 03:55, maxiangqian wrote:

/bin/tar: /data/test/PG_9.0_201008051/20001/2451: Cannot stat: No such
file or directory

/bin/tar: Error exit delayed from previous errors


and /data/test/PG_9.0_201008051/20001/2451 file is not exist.



It looks like you are trying to make a filesystem backup while the files are 
still being updated. In order to make a filesystem backup you need to either:

   - shutdown the database
   - run "pg_start_backup" (make SURE that you run pg_stop_backup" when done)

http://www.postgresql.org/docs/9.3/static/backup-file.html
http://www.postgresql.org/docs/9.3/static/continuous-archiving.html#BACKUP-TIPS


It's actually perfectly normal for files to disappear during a backup, 
even when pg_start_backup() is called first (never perform file-level 
backup with calling pg_start_backup()).  The database continues to 
operation normally during a backup so any dropped table (or temp table) 
will result it files being removed by Postgres, not to mention changes 
in clog, pg_xlog, etc.


All of this is reconciled during the replay of WAL after a restore.  You 
just need to ignore any missing file errors while taking the backup and 
be sure to back up all the WAL segments required to make the database 
consistent. pg_basebackup will do all of this for you.


Another option is to use purpose-built backup software such as 
pgBackRest or barman, both of which are open source.


--
-David
da...@pgmasters.net


--
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] Broken primary key after backup restore.

[David Steele]

On 9/18/15 3:44 PM, Michael Chau wrote:

Hi Jeff,


Only if you are very lucky.  If your tar command tars up the pg_xlog directory 
as the last thing it does, then you are probably going to be OK.  Otherwise, it 
is a crap shoot.


May be that's it. I have another similar set up, but the pg_xlog is a
soft link to another directory, and I use 'tar -chvzf'. It tar up the
pg_xlog at the very last. And the restore is fine.


This is still not always safe.  It depends on your wal_keep_segments 
settings and some luck.  WAL segments can be recycled during the backup.



For this one, DB1 and DB2, the pg_xlog is the directory itself, and I
use 'tar -cvzf'. And it tar up pg_xlog at the beginning. I always have
doubt about it. But I though pg_stop_backup() and pg_start_backup() like
freezing would prevent the inconsistency.


This is definitely not a good idea.


Indeed, I will look inot pgbasebackup.


pg_basebackup is good for creating replicas but for real backup you 
might want to consider purpose-built backup software like pgBackRest or 
barman.


--
-David
da...@pgmasters.net


--
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] error on online backup using pg_basebackup tool

[David Steele]

On 9/3/15 5:07 PM, Joseph Kregloh wrote:
> You can always move the folder out of there. Depending on the version of
> PostgreSQL different steps need to be take. If I remember correctly for
> version 9.3 you can move the symlink to point to the new directory. Pre
> 9.3 you need to move the symlink and the pointer inside the pg tables.

You can move the directory and modify the symlink on 9.2 or later
without any additional complications.

However - and this is very important - PostgreSQL must be shut down
during this operation.

My recommendation would be to move both tablespaces out of $PGDATA.
There has been a lot of discussion about disallowing it in future
releases as it causes problems for various tools.

-- 
-David
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] pg_start_backup: file has vanished from pg_subtrans/

[David Steele]

On 8/7/15 7:01 AM, Leo Baltus wrote:

While backing up some postgresql-8.4.2 instances using
pg_start_backup()/pg_stop_backup()

We noticed that sometimes rsync complains:

file has vanished: "./data/pg_subtrans/3A37"
rsync warning: some files vanished before they could be transferred (code 24) 
at main.c(1039) [sender=3.0.6


It's perfectly normal for Postgres to remove files during a backup.  If 
a table is dropped then the files storing that table will be deleted. 
Files can also be removed from pg_subtrans once they are no longer needed.



We already have excluded pgsql_tmp/ pg_xlog/ and pg_stat_tmp/ so I was
tempted to exclude pg_subtrans/ as well. However some googling seems to
suggest that this might invalidate the backup:


I would recommend only excluding pg_xlog as recommended in the Postgres 
docs.



https://groups.google.com/forum/#!msg/pgbarman/zQxg6pOXkZ8/AmTmVtUA3dEJ

I am not sure what to believe, either
  - my backup was invalid and I have to take measures to prevent this
(and file a bug report)


Your backup should be good so long as you've captured all the WAL.


  - I can ignore this *and* can safely exclude pg_trans/ from my backups.
  - there is some other workaround...


Do *not* exclude pg_subtrans.

You may also want to look at backup software such as pgBackRest.  It 
understands that files can be removed during backup and does not emit 
warnings when it happens (though info messages are still logged 
depending on your log level).


--
-David
da...@pgmasters.net


--
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] Backup Method

[David Steele]

On 7/3/15 8:08 AM, howardn...@selestial.com wrote:
> Thanks everyone.
>
> I am trying to move away from pg_dump as it is proving too slow. The
> size of the database clusters are approaching 1TB (with multiple
> individual compressed pg_dumps of around 100GB each, but the pace of
> change is relatively glacial compared to the size so I am hoping that
> WAL backups will prove to be much more efficient. As you all point out
> it looks like I will need to test the various methods to find the best
> solution for me.

PgBackRest might be a good fit for you:
https://github.com/pgmasters/backrest

It supports compressed full, differential, and incremental backups and
can do local backups to an NFS mount without a lot of configuration.

> A supplementary question would be: would rsync be a viable alternative
> to pg_basebackup when performing the file system copy. I have seen a
> few posts on this subject which suggest rsync is more prone to
> mistakes but  is potentially the faster option. Love to hear all your
> thoughts on this subject before I risk trying rsync.

Rsync is potentially dangerous for an incremental (or resumed) backup. 
PgBackRest is not based on rsync and is safe for incrementals.  It also
offers destination compression which rsync cannot do, though of course
you could backup to a compressed file system.

More importantly you still need to deal with the WAL and PgBackRest does
that for you.  If you have a large database with a small rate of change
then a weekly/biweekly full backup with daily incremental/differential
should be very efficient.

-- 
- David Steele
da...@pgmasters.net




signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Performance on DISABLE TRIGGER

[David Steele]

On 2/27/15 9:07 AM, gmb wrote:
> Hi all
> 
> I'm doing some maintenance - which is done quite often, never had this
> problem before - which requires me to disable triggers, run some updates and
> then re-enable the triggers. 
> Where the whole process normally take 30 sec , it took much longer today and
> I cancelled after 5 minutes.
> 
> After running the statements individually to pinpoint the issue , I
> identified that the problem is on the first line.
>  >> ALTER TABLE tab DISABLE TRIGGER trig;
> I have not been able to run this successfully -- after 10 minutes the
> connection ( to remote DB ) got severed .
> Any ideas on where I can start to look for the cause of the problem ? 

ALTER TABLE requires an exclusive lock - my guess is that another
process has a lock on the table.  It could even be a select.

pg_locks is your friend in this case:
http://www.postgresql.org/docs/9.4/static/view-pg-locks.html

-- 
- David Steele
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] ANALYZE after CREATE TABLE AS SELECT...

[David Steele]

On 2/26/15 5:23 PM, Alvaro Herrera wrote:
> Semyon Reyfman wrote:
> 
>> When I create a table with "CREATE TABLE name AS SELECT." statement and
>> immediately afterward use this new table in a query does it make sense to
>> run ANALYZE on the table in between?  It appears that postgres should be
>> able to collect very detailed statistics while emitting the table but I am
>> not sure if this is done.
> 
> It isn't.  It also doesn't create any indexes, which you might want to
> do before analyze.

Is it necessary to create indexes before analyzing?

I usually do, just to be safe, but I thought statistics were based
solely on sampling of the heap.

-- 
- David Steele
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] ANALYZE after CREATE TABLE AS SELECT...

[David Steele]

On 2/26/15 2:05 PM, Tom Lane wrote:
> "Semyon Reyfman"  writes:
>> When I create a table with "CREATE TABLE name AS SELECT." statement and
>> immediately afterward use this new table in a query does it make sense to
>> run ANALYZE on the new table in between?
> 
> Yes.

Yes.  And to be more specific - vacuum cannot see tables that have been
created in a transaction, so you should always analyze tables that you
intend to read in the same transaction where you created them.

Even if you do commit before reading there is a race condition with
vacuum, so it's best to analyze.

Bonus tip: the same is true for any temp tables you might create.  More
so, since vacuum will never see them at all.

-- 
- David Steele
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Locking during UPDATE query with SUBSELECT

[David Steele]

On 2/25/15 10:49 AM, Cenkar, Maciej wrote:
> Given PostgreSQL 9.3.5 what is locking strategy when executing query
> such as:
> 
> UPDATE table SET some_col = some_val WHERE id IN (SELECT id FROM
> expensive_query_with_joins).
> 
> Is this starting to lock rows after it executed sub-select or is it
> locking whole table and then executing select?

This statement will lock rows in the update table as they are returned
from the subquery and modified - only a share lock will be held on the
entire table from the beginning (that just keeps people from modifying
the table while you are using it).  If the subquery contains a group by,
order by, or some other clause that requires all the returned rows to be
examined as a whole then the row locks will happen very consecutively,
otherwise they could happen over a longer period of time and the locks
will be held for longer.

> Is there any advantage in precomputing ids from nested select to run only
> 
> UPDATE table SET some_col = some_val WHERE id IN (precomputed_values)?

If your subquery is very expensive but returns a reasonable number of
rows, then putting the results in a temp table and then updating from
the temp table may allow locks to be held a shorter amount of time.  If
your subquery contains a group by, order by, etc. as mentioned above
then I wouldn't bother.

One other thing to mention - since the order of updates cannot be
guaranteed I wouldn't run more than one update like this at the same
time or you might get deadlocks.

-- 
- David Steele
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Row-level Security vs Application-level authz

[David Steele]

On 2/24/15 3:31 PM, Stephen Frost wrote:
> * David Steele (da...@pgmasters.net) wrote:
>> On 2/24/15 3:07 PM, Stephen Frost wrote:
>>> The problem with a temporary table is, well, it goes away. :)  There are
>>> further concerns that, because it's created in some fashion by the
>>> single application user, it might be less secure.  Really, though, I'd
>>> want it to be real so that it could have constraints be on it which
>>> reference other appropriate tables, so the web user doesn't have to have
>>> rights in any fashion to create objects, and so that it can be
>>> referenced from RLS policies.  A table as transient as a temporary table
>>> doesn't strike me as the right solution for that.
>>
>> Temp tables go away at the end of the session, sure.  It seems like
>> exactly the time when you'd want them to do so.
>>
>> If the temp table is created by a security definer function (as was
>> suggested earlier) then no special user privs are required.
>>
>> Being referenced from RLS polices is a good argument, though.  I guess
>> that's not possible with a temp table?  Are they pre-parsed?
> 
> Actually, it is possible, but it creates a dependency on the temporary
> table and when the temporary table goes away, so will the policy.  This
> isn't a huge issue for RLS, of course, as if there's no policy then the
> default-deny policy will kick in, but you can't have a policy against a
> temporary table continue past the end of that session.

It's not good if policies go away, even with a default.

> Following the earlier discussion, I suppose you could create both the
> temporary table and the policies in the security definer function, but
> it feels a lot cleaner to have a real table for all of that, in my view,
> to keep that security definer function nice and simple.

Then you'd have a policy for each user which sounds messy, or some crazy
locking scheme which sounds worse.  I agree that a real table sounds
like a better solution.

> Further, there's lots of other reasons to have a session table anyway,
> from an application standpoint, and so this feels like an approach which
> is more in-line with how the application likely wants to operate anyway.
> It's also handy to be able to log into the database and see all the
> current sessions, similar to how we have pg_stat_activity.

Well, I am a fan of monitoring.

So I guess my last question is if you are inserting rows into a table to
track user connections, how do you clean them out when the client does
not disconnect cleanly?  Or is this table intended to be append-only?

-- 
- David Steele
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Row-level Security vs Application-level authz

[David Steele]

On 2/24/15 3:07 PM, Stephen Frost wrote:
> * David Steele (da...@pgmasters.net) wrote:
>> On 2/23/15 8:16 PM, Stephen Frost wrote:
>>> * David G. Johnston (david.g.johns...@gmail.com) wrote:
>>>> I take it that the table has to be permanent otherwise you would have
>>>> suggested
>>>> and unlogged temporary table as the target...
>>>
>>> A temporary table would have to be recreated each time and that'd be
>>> less than ideal.  You can use a single unlogged table which includes the
>>> backend pid (which can be acquired through a function call) to keep
>>> track of which user is logged in on a given backend at a given point in
>>> time.
>>
>> It's not clear to me why creating a temp table per session would be less
>> than ideal.  I've certainly used session-scope temp tables to good
>> effect a number of times.  Transaction-scope would be another story of
>> course.
>>
>> Am I missing something?
> 
> The problem with a temporary table is, well, it goes away. :)  There are
> further concerns that, because it's created in some fashion by the
> single application user, it might be less secure.  Really, though, I'd
> want it to be real so that it could have constraints be on it which
> reference other appropriate tables, so the web user doesn't have to have
> rights in any fashion to create objects, and so that it can be
> referenced from RLS policies.  A table as transient as a temporary table
> doesn't strike me as the right solution for that.

Temp tables go away at the end of the session, sure.  It seems like
exactly the time when you'd want them to do so.

If the temp table is created by a security definer function (as was
suggested earlier) then no special user privs are required.

Being referenced from RLS polices is a good argument, though.  I guess
that's not possible with a temp table?  Are they pre-parsed?

-- 
- David Steele
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Row-level Security vs Application-level authz

[David Steele]

On 2/23/15 8:16 PM, Stephen Frost wrote:
> * David G. Johnston (david.g.johns...@gmail.com) wrote:
>> I take it that the table has to be permanent otherwise you would have
>> suggested
>> and unlogged temporary table as the target...
> 
> A temporary table would have to be recreated each time and that'd be
> less than ideal.  You can use a single unlogged table which includes the
> backend pid (which can be acquired through a function call) to keep
> track of which user is logged in on a given backend at a given point in
> time.

It's not clear to me why creating a temp table per session would be less
than ideal.  I've certainly used session-scope temp tables to good
effect a number of times.  Transaction-scope would be another story of
course.

Am I missing something?

-- 
- David Steele
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Postgres architecture for multiple instances

[David Steele]

On 2/21/15 6:08 PM, Adrian Klaver wrote:
> On 02/21/2015 02:01 PM, Samuel Smith wrote:
>> Howdy,
>> I am looking for advice on migrating to postgres from another database
>> system.
>>
>> Without going into too much detail, my company offers a software
>> solution which we self host ourselves in our data center. We have gotten
>> a green light from management to start using postgres as a free and
>> cheaper database. Normally the application connects to a DB2 database.
>>
>> The current architecture has multiple DB2 databases installed on a
>> single linux host (on top of vmware with 4 vcpus). Each DB2 instance
>> runs as a different local linux user and only manages one database.
>> Normally there are less than 5 DB2 instances per host.
>>
>> My question is, if I am going to replace DB2 as the database with
>> Postgres, should I keep to this architecture of each customer
>> application gets their own database instance? What I was thinking would
>> be much more simpler would be to have multiple databases under one
>> postgres instance (which is how most linux distros install postgres
>> anyway). Having multiple databases under one instance seems to be a much
>> easier way. Of course then the issue becomes if there is an issue with
>> this single instance, multiple customers will be affected.

Certainly more than one customer may be affected by an outage, but there
are also fewer instances to be managed.  This can be particularly
important for upgrades - fewer systems to upgrade can be a good thing.

> The issue that has come up in previous discussions on this subject is
> that there is global meta data available to all users in a Postgres
> cluster(instance), in particular the database users. So while it is
> possible to restrict access to the database itself, it is more difficult
> if not impossible to restrict access to information about the other
> databases in the cluster.

This is only a problem if the customer has direct access to the
database.  If they are connecting through a software layer then
multi-tenant solutions are practical.

>> For the most part, I am completely on the fence about deciding either
>> way. What does the postgres community recommend in this situation? All
>> docs and books that I read seem to suggest that a postgres instance
>> manages databases (plural!) and normally there would be only one
>> postgres instance running per host.
>>
>> I am also interested in the best way forward with failover and high
>> availability.
> 
> Currently the built in replication solutions work at the cluster level,
> not at the database level. There are third party tools, Slony and
> Bucardo come to mind, that can work at a more focused level.

Again, it depends in what kind of access your customers have to the
database.  Logical backups can be done on a per-database basis (and are
very practical for small datasets), but physical backups (which are more
efficient) must backup the entire cluster (except for the third-party
solutions noted above that have their own challenges).

You said you don't want to give a lot of information, but the optimal
solution depends on how your customers connect to their database:

1) Entirely through an application or API
2) Directly to the db via psql, PgAdmin, JDBC, etc.

Without that information it's hard to give really solid advice.

-- 
- David Steele
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature