Re: [GENERAL] pg_dump system catalog

[Achilleas Mantzios]

On 28/11/2016 19:21, Melvin Davidson wrote:

On Mon, Nov 28, 2016 at 10:50 AM, Adrian Klaver > wrote:

On 11/28/2016 07:44 AM, Melvin Davidson wrote:





*To clarify, you cannot dump the pg_catalog schema. It is the main
control of how all other objects are


Actually you can. I would not of thought so, but tried Achilleas's 
suggestion and it worked. Example:

--
-- Name: pg_aggregate; Type: TABLE; Schema: pg_catalog; Owner: postgres
--

CREATE TABLE pg_aggregate (
aggfnoid regproc NOT NULL,
aggkind "char" NOT NULL,
aggnumdirectargs smallint NOT NULL,
aggtransfn regproc NOT NULL,
aggfinalfn regproc NOT NULL,
aggmtransfn regproc NOT NULL,
aggminvtransfn regproc NOT NULL,
aggmfinalfn regproc NOT NULL,
aggfinalextra boolean NOT NULL,
aggmfinalextra boolean NOT NULL,
aggsortop oid NOT NULL,
aggtranstype oid NOT NULL,
aggtransspace integer NOT NULL,
aggmtranstype oid NOT NULL,
aggmtransspace integer NOT NULL,
agginitval text,
aggminitval text
);

COPY pg_aggregate (aggfnoid 


and you do get errors:

pg_dump: WARNING: typtype of data type "any" appears to be invalid
pg_dump: WARNING: typtype of data type "anyarray" appears to be invalid
pg_dump: WARNING: typtype of data type "anyelement" appears to be invalid
pg_dump: WARNING: typtype of data type "anyenum" appears to be invalid
pg_dump: WARNING: typtype of data type "anynonarray" appears to be invalid
pg_dump: WARNING: typtype of data type "anyrange" appears to be invalid
pg_dump: WARNING: typtype of data type "cstring" appears to be invalid



Still not sure why you would want to, but you can.


stored in the cluster. There is no point in dumping it and all it's
tables and views are already clearly
documented.
https://www.postgresql.org/docs/9.6/static/catalogs.html 


pg_catalog itself is generated with the initdb command when a new
postgresql cluster is generated.
https://www.postgresql.org/docs/9.6/static/creating-cluster.html 

https://www.postgresql.org/docs/9.6/static/app-initdb.html* 


--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.



-- 
Adrian Klaver

adrian.kla...@aklaver.com 


*Hmmm, well you learn something new every day. Albeit, although you can view 
the dump file, I'm pretty sure you cannot restore it to a database because,
*
*by definition, those tables would already exist in the database.*


That's right I was kind of suspicious about the OP's intentions. I agree, no 
user should attempt to restore anything from this dump, unless (s)he is a pgsql 
hacker and knows exactly what he's doing.



--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: [GENERAL] Backup "Best Practices"

[Thomas Kellerer]

Israel Brewster schrieb am 28.11.2016 um 23:50:


pg_archivecleanup -n /mnt/server/archiverdir 
00010010.0020.backup


Ok, but where does that "00010010.0020.backup"
come from? I mean, I can tell it's a WAL segment file name (plus a
backup label), but I don't have anything like that in my WAL
archives, even though I've run pg_basebackup a couple of times. Do I
have to call something to create that file? Some flag to
pg_basebackup? At the moment I am running pg_basebackup such that it
generates gziped tar files, if that makes a difference.


The .backup file will be inside the tar file if I'm not mistaken




--
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 "Best Practices"

[Jeff Janes]

On Mon, Nov 28, 2016 at 2:50 PM, Israel Brewster 
wrote:

>
> - What is the "best" (or just a good) method of keeping the WAL archives
>> under control? Obviously when I do a new basebackup I can "cleanup" any old
>> files that said backup doesn't need,
>>
>
> You have said you might be interested in doing PITR. So you want to delay
> the cleanup so as to not compromise that ability.  You need to develop a
> policy on how far back you want to be able to do a PITR.
>
>
>
>> but how do I know what those are?
>>
>
> pg_archivecleanup -n /mnt/server/archiverdir 00010010.
> 0020.backup
>
>
> Ok, but where does that "00010010.0020.backup" come
> from? I mean, I can tell it's a WAL segment file name (plus a backup
> label), but I don't have anything like that in my WAL archives, even though
> I've run pg_basebackup a couple of times.
>

I get one file like that for every pg_basebackup I run.  Could your
archive_command be doing something to specifically short-circuit the
writing of those files?  Like testing the length of %p or %f?




> Do I have to call something to create that file? Some flag to
> pg_basebackup? At the moment I am running pg_basebackup such that it
> generates gziped tar files, if that makes a difference.
>


That is how I run it as well.  I don't think there is a flag to
pg_basebackup which even allows you to bypass the creation of those files.
You are looking in the WAL archive itself, correct?  Not somewhere in a
listing of the base.tar.gz file?

Cheers,

Jeff

Re: [GENERAL] Wal files - Question | Postgres 9.2

[David G. Johnston]

On Mon, Nov 28, 2016 at 8:22 PM, Patrick B  wrote:

>
> Ho
> ​[w]
>  is that even possible?? I don't understand!
>
>
​https://www.postgresql.org/docs/9.2/static/warm-standby.html
"""​

If you use streaming replication without file-based continuous archiving,
you have to set wal_keep_segments in the master to a value high enough to
ensure that old WAL segments are not recycled too early, while the standby
might still need them to catch up. If the standby falls behind too much, it
needs to be reinitialized from a new base backup. If you set up a WAL
archive that's accessible from the standby, wal_keep_segments is not
required as the standby can always use the archive to catch up.
​"""

Basically you did just that when you destroyed the archive.  Apparently the
master doesn't churn through WAL quickly enough to have had to discard the
segments from the prior two hours.

David J.
​

Re: [GENERAL] Wal files - Question | Postgres 9.2

[Patrick B]

2016-11-29 15:21 GMT+13:00 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/).
>
>
> Thanks for all the replies guys.

Related to the "cp" - That's fine. I'm just doing this in a test
environment. On my prod servers I use wal-e(aws s3) and bash
script(locally) to do this work.

I've done some tests, that only gave me more questions. I'll put everything
I done here and hopefully you guys will be able to help me to understand it.

1. Set up a pg-test-01 machine on Ubuntu 16 Server
2. installed Postgres 9.2 + contribs + libs  + etc
3. Configured postgres to be a streaming replication slave (postgresql.conf
and recovery.conf)
4. Configured my masterdb01 server to ship wal_files to the pg-test-01
server
5. Made a pg_basebackup on my master DB server (DB is 1TB big)
6. Recovered the basebackup on the pg-test-01 machine
7. Postgres started to recover the wal_files and then streaming replication
worked:

LOG:  streaming replication successfully connected to primary


8. Stopped postgres on the pg-test-01 server for 10 minutes
9. Started postgres on the pg-test-01 to see if the wal_files would be
recovered

LOG:  restored log file "00021B2F004F" from archive

LOG:  restored log file "00021B2F0050" from archive

LOG:  restored log file "00021B2F0051" from archive

LOG:  restored log file "00021B2F0052" from archive


10. Removed all the wal_files from pg-test-01 (No wal_files are being
shipped to this server anymore)
11. Stopped postgres for 2 hours
12. Started postgres as streaming replication

cp: cannot stat 'archive/00022B3600BB': No such file or
> directory
> cp: cannot stat 'archive/00022B3600BC': No such file or
> directory
> cp: cannot stat 'archive/00022B3600BD': No such file or
> directory
> LOG:  streaming replication successfully connected to primary



Postgres was stopped for 2h. How can postgres connect using streaming
replication, if no wal_files were in there???

Hos is that even possible?? I don't understand!

Thanks
Patrick

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] Storing files: 2.3TBytes, 17M file count

[Mike Sofen]

From: Thomas Güttler   Sent: Monday, November 28, 2016 6:28 AM



...I have 2.3TBytes of files. File count is 17M

Since we already store our structured data in postgres, I think about storing 
the files in PostgreSQL, too.

Is it feasible to store file in PostgreSQL?

---

I am doing something similar, but in reverse.  The legacy mysql databases I’m 
converting into a modern Postgres data model, have very large genomic strings 
stored in 3 separate columns.  Out of the 25 TB of legacy data storage (in 800 
dbs across 4 servers, about 22b rows), those 3 columns consume 90% of the total 
space, and they are just used for reference, never used in searches or 
calculations.  They range from 1k to several MB.

 

Since I am collapsing all 800 dbs into a single PG db, being very smart about 
storage was critical.  Since we’re also migrating everything to AWS, we’re 
placing those 3 strings (per row) into a single json document and storing the 
document in S3 bins, with the pointer to the file being the globally unique PK 
for the row…super simple.  The app tier knows to fetch the data from the db and 
large string json from the S3 bins.  The retrieval time is surprisingly fast, 
this is all real time web app stuff.

 

This is a model that could work for anyone dealing with large objects (text or 
binary).  The nice part is, the original 25TB of data storage drops to 5TB – a 
much more manageable number, allowing for significant growth, which is on the 
horizon.

 

Mike Sofen  (Synthetic Genomics USA)

Re: [GENERAL] Backup "Best Practices"

[Israel Brewster]

On Nov 28, 2016, at 2:13 PM, John R Pierce  wrote:
> 
> On 11/28/2016 2:50 PM, Israel Brewster wrote:
>>> 
>>> Why wouldn't the streaming replica salvage that?  Are they expected to fail 
>>> together?  Is the NFS share onto which you want to store your basebackup 
>>> and WAL also expected to fail together with them?
>> 
>> That's why I specified *total* failure. If only the primary dies, that's a 
>> simple cutover to the secondary, and not a total failure - no need to go to 
>> backups of any sort under that scenario :-) I'm thinking more along the 
>> lines of server room burns down, natural disaster, etc - something that 
>> causes a total failure of the db system, necessitating recovery from an 
>> offsite backup.
> 
> your base backups and WAL archive should be replicated offsite, then.
> 
> 

Obviously :-)
> 
> -- 
> john r pierce, recycling bits in santa cruz

Re: [GENERAL] hot_standby_feedback

[Andres Freund]

On 2016-11-28 22:14:55 +0100, Torsten Förtsch wrote:
> Hi,
> 
> I am in the process of reviewing our configs for a number of 9.3 databases
> and found a replica with hot_standby_feedback=on. I remember when we set it
> long ago we were fighting cancelled queries. I also remember that it never
> really worked for us. In the end we set up 2 replicas, one suitable for
> short queries where we prefer low replication lag, and another one where we
> allow for long running queries but sacrifice timeliness
> (max_standby_*_delay=-1).

There's a few kind of conflicts against which hs_feedback doesn't
protect. E.g. exclusive locks on tables that are in use and such
(e.g. by vacuum truncating a table or an explicit drop table).

There's a table with some information about the causes of cancellations,
pg_stat_database_conflicts - did you check that?

> I have a hunch why hot_standby_feedback=on didn't work. But I never
> verified it. So, here it is. The key is this sentence:
> 
> "Feedback messages will not be sent more frequently than once per
> wal_receiver_status_interval."
> 
> That interval is 10 sec. So, assuming a transaction on the replica uses a
> row right after the message has been sent. Then there is a 10 sec window in
> which the master cannot know that the row is needed on the replica and can
> vacuum it. If then the transaction on the replica takes longer than
> max_standby_*_delay, the only option is to cancel it.
> 
> Is that explanation correct?

No. That just means that we don't update the value more frequently. The
value reported is a "horizon" meaning that nothing older than the
reported value can be accessed.

Greetings,

Andres Freund


-- 
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 "Best Practices"

[Israel Brewster]

On Nov 25, 2016, at 1:00 PM, Jeff Janes  wrote:
> 
> On Wed, Nov 23, 2016 at 10:16 AM, Israel Brewster  > wrote:
> I was wondering if someone could inform me about, or point me to an online 
> article about best practices for backing up a postgresql database cluster? At 
> the moment, I have two servers running with streaming replication for 
> failover purposes, and then I do nightly database dumps for recovery/backup 
> purposes. However, I feel this approach is lacking in a couple areas I can 
> think of:
> 
> - If a total failure occurs towards the end of the day, we could potentially 
> loose a whole days worth of data.
> 
> Why wouldn't the streaming replica salvage that?  Are they expected to fail 
> together?  Is the NFS share onto which you want to store your basebackup and 
> WAL also expected to fail together with them?

That's why I specified *total* failure. If only the primary dies, that's a 
simple cutover to the secondary, and not a total failure - no need to go to 
backups of any sort under that scenario :-) I'm thinking more along the lines 
of server room burns down, natural disaster, etc - something that causes a 
total failure of the db system, necessitating recovery from an offsite backup.

>  
> Similar argument for user error - there is no way to "undo" a catastrophic 
> user data error without going all the way back to the previous day
> - Less-than-ideal recovery under some scenarios. Since each database in the 
> cluster is dumped individually, this is good should only *one* database need 
> to be restored, but could get tedious should the entire cluster need to be 
> restored.
> 
> To mitigate these issues, I am thinking of supplementing the individual dumps 
> with a full base backup and WAL archiving to a NFS share. This should enable 
> (relatively) quick/easy recovery from backup, plus the ability to do PIT 
> Recovery. I do have a few questions with this approach, however:
> 
> - How do I figure out how often I should take a full base backup? I know this 
> will depend on the amount of traffic my database is doing, and how long I am 
> willing to wait for WAL files to be replayed - the more WAL files needing 
> replayed, the longer recovery will take - but is there some rule of thumb 
> that I can use to calculate how often I need a new base backup? Perhaps based 
> on the number of WAL files?
> 
> You have to try it and see.  Different types of wal records will take 
> different amounts of time to re-play, so there is no rule of thumb. It would 
> depend on the type of traffic you have in your database.  And it could be 
> limited by a single CPU, or by IO.  If the restore_command needs to restore 
> the WAL from a remote server, it is very likely to be limited by the latency 
> of doing that.  In fact, this is often the bottleneck even if it is restoring 
> from the local server, at least if archival is often driven by 
> archive_timeout.
> 
> When I need to re-clone production to get a fresh server to use for dev or 
> testing, I do so using almost exactly the same method I would use for 
> restoring production from a disaster (restore from most recent basebackup, 
> then recovery from WAL archive).  So I know how long it takes for the 
> recovery to happen based on true experience, and I take a new basebackup when 
> that length of time starts to annoy me.

Gotcha. Guess I'll have to start running some tests :-)

>  
> - What is the "best" (or just a good) method of keeping the WAL archives 
> under control? Obviously when I do a new basebackup I can "cleanup" any old 
> files that said backup doesn't need,
> 
> You have said you might be interested in doing PITR. So you want to delay the 
> cleanup so as to not compromise that ability.  You need to develop a policy 
> on how far back you want to be able to do a PITR.
> 
>  
> but how do I know what those are?
> 
> pg_archivecleanup -n /mnt/server/archiverdir 
> 00010010.0020.backup

Ok, but where does that "00010010.0020.backup" come from? I 
mean, I can tell it's a WAL segment file name (plus a backup label), but I 
don't have anything like that in my WAL archives, even though I've run 
pg_basebackup a couple of times. Do I have to call something to create that 
file? Some flag to pg_basebackup? At the moment I am running pg_basebackup such 
that it generates gziped tar files, if that makes a difference.

> 
>  
> - Should I be looking at any other backup methods in addition to/instead of 
> the basebackup/WAL archive scheme?
> 
> 
> You may want to consider pg_receivexlog to maintain your WAL archive, rather 
> than archive_command.  That way you don't have to worry about the trades off 
> caused by setting archive_timeout.  But unless you use it with a replication 
> slot, it is not very safe as the pg_receivexlog could stop working and your 
> database would happy run along without protection.  

Re: [GENERAL] FOR UPDATE

[said assemlal]

> Why do you want to lock these results?

Because we are migrating very old pipeline where we save data in file
texts. So we have decided to keep some logics inside perl code. That means
to update a record:
 1. we have to read/lock it
 2. return the result to the application
 3. the application does its business and return the new value to the
database
 4. we update the record.


> What do you mean "empty"?  If you pass the empty string, the query will
> return rows that have the empty string in those columns.  Note that the
> NULL value is not the same as the empty string.  If the function is
> defined as STRICT (also spelled RETURNS NULL ON NULL INPUT), then the
> function is not even called if you pass NULL arguments, and it simply
> returns NULL without locking anything.

By empty I mean: ''

> Not necessarily ... depends on what you want to happen.

It is a migration case so I was thinking to take the more safe way..



So I think the behaviour is clear to me now.

Thank you.

On Mon, Nov 28, 2016 at 4:37 PM, Alvaro Herrera 
wrote:

> said assemlal wrote:
> > Hello,
> >
> > PG: 9.4
> > CentOS 6
> >
> > I am writing functions to lock results.
> >
> > Let's take an example:
> >
> > CREATE OR REPLACE FUNCTION usp_locking_my_result($1 VARCHAR(50), $2
> > VARCHAR(50))
> > RETURNS TEXT AS $$
> > SELECT value
> > FROM my_table
> > WHERE field1 = $1 AND field2 = $2 FOR UPDATE;
> > $$ LANGUAGE SQL;
>
> Why do you want to lock these results?
>
> > What happens if one of those arguments are empty and database finds
> > results?  ( I think they will locked )
>
> What do you mean "empty"?  If you pass the empty string, the query will
> return rows that have the empty string in those columns.  Note that the
> NULL value is not the same as the empty string.  If the function is
> defined as STRICT (also spelled RETURNS NULL ON NULL INPUT), then the
> function is not even called if you pass NULL arguments, and it simply
> returns NULL without locking anything.
>
> > Should I check the inputs and raise an exception if there are empty ?
>
> Not necessarily ... depends on what you want to happen.
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: [GENERAL] FOR UPDATE

[Alvaro Herrera]

said assemlal wrote:
> Hello,
> 
> PG: 9.4
> CentOS 6
> 
> I am writing functions to lock results.
> 
> Let's take an example:
> 
> CREATE OR REPLACE FUNCTION usp_locking_my_result($1 VARCHAR(50), $2
> VARCHAR(50))
> RETURNS TEXT AS $$
> SELECT value
> FROM my_table
> WHERE field1 = $1 AND field2 = $2 FOR UPDATE;
> $$ LANGUAGE SQL;

Why do you want to lock these results?

> What happens if one of those arguments are empty and database finds
> results?  ( I think they will locked )

What do you mean "empty"?  If you pass the empty string, the query will
return rows that have the empty string in those columns.  Note that the
NULL value is not the same as the empty string.  If the function is
defined as STRICT (also spelled RETURNS NULL ON NULL INPUT), then the
function is not even called if you pass NULL arguments, and it simply
returns NULL without locking anything.

> Should I check the inputs and raise an exception if there are empty ?

Not necessarily ... depends on what you want to happen.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[GENERAL] hot_standby_feedback

[Torsten Förtsch]

Hi,

I am in the process of reviewing our configs for a number of 9.3 databases
and found a replica with hot_standby_feedback=on. I remember when we set it
long ago we were fighting cancelled queries. I also remember that it never
really worked for us. In the end we set up 2 replicas, one suitable for
short queries where we prefer low replication lag, and another one where we
allow for long running queries but sacrifice timeliness
(max_standby_*_delay=-1).

I have a hunch why hot_standby_feedback=on didn't work. But I never
verified it. So, here it is. The key is this sentence:

"Feedback messages will not be sent more frequently than once per
wal_receiver_status_interval."

That interval is 10 sec. So, assuming a transaction on the replica uses a
row right after the message has been sent. Then there is a 10 sec window in
which the master cannot know that the row is needed on the replica and can
vacuum it. If then the transaction on the replica takes longer than
max_standby_*_delay, the only option is to cancel it.

Is that explanation correct?

What is the correct way to use hot_standby_feedback to prevent
cancellations reliably? (and accepting the bloat)

Thanks,
Torsten

[GENERAL] FOR UPDATE

[said assemlal]

Hello,

PG: 9.4
CentOS 6

I am writing functions to lock results.

Let's take an example:

CREATE OR REPLACE FUNCTION usp_locking_my_result($1 VARCHAR(50), $2
VARCHAR(50))
RETURNS TEXT AS $$
SELECT value
FROM my_table
WHERE field1 = $1 AND field2 = $2 FOR UPDATE;
$$ LANGUAGE SQL;


What happens if one of those arguments are empty and database finds
results?  ( I think they will locked )

Should I check the inputs and raise an exception if there are empty ?

Thank,
Saïd

Re: [GENERAL] Storing files: 2.3TBytes, 17M file count

Thomas Güttler  wrote:
> 
> Up to now, we don't store files in PostgreSQL.
> I was told, that you must not do this  But this was 20 years ago.
> I have 2.3TBytes of files. File count is 17M
> Up to now we use rsync (via rsnapshot) to backup our data.
> But it takes longer and longer for rsync to detect the changes. Rsync checks 
> many files. But daily only very few files really change. More than 99.9% 
> don't.
> Since we already store our structured data in postgres, I think about storing 
> the files in PostgreSQL, too.
> What is the current state of the art?
> Is it feasible to store file in PostgreSQL?
> Are there already projects which use PostgreSQL as storage backend?
> I have the hope, that it would be easier to backup only the files which 
> changed.

There are a lot of "it depends" in answering that. :)

The biggest question is "What kind of files are they?" Text or binary data?
If they're text, you could compress them and store them in
the DB (I did that successfully on 1 project with XML as that was the fastest
answer from our benchmarking of all combinations of in DB, on the file system,
compressed or not). Then again, our files were from 1-10KB each.

If I did the math right, your files average ~135GB each. That argues for leaving
them on the file system and storing the name. Have you considered adding data
for the filename and a timestamp, then having a scheduled task that selects
the files with a timestamp more recent than the last time the transfer ran
and rsync'ing (or whatever tool you want) only those? That's mostly application 
code
and not DB code, but I'd think that'd be the easiest and fastest all things
considered.

HTH,
Kevin
-- 
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] Query with large in clauses uses a lot of memory

[greigwise]

Wow.  Thanks for the prompt answer.

As a follow-up I was wondering if maybe there would be a way to tell it to
NOT try to plan/execute the query (and instead throw an error) if the memory
usage exceeded X.

Thanks again.

Greig



--
View this message in context: 
http://postgresql.nabble.com/Query-with-large-in-clauses-uses-a-lot-of-memory-tp5931716p5932279.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Storing files: 2.3TBytes, 17M file count

[Eduardo Morras]

On Mon, 28 Nov 2016 15:28:28 +0100
Thomas Güttler  wrote:

> Hi,
> 
> Up to now, we don't store files in PostgreSQL.
> 
> I was told, that you must not do this  But this was 20 years ago.
> 
> 
> I have 2.3TBytes of files. File count is 17M
> 
> Up to now we use rsync (via rsnapshot) to backup our data.
> 
> But it takes longer and longer for rsync to detect
> the changes. Rsync checks many files. But daily only
> very few files really change. More than 99.9% don't.
> 
> Since we already store our structured data in postgres, I think
> about storing the files in PostgreSQL, too.
> 
> What is the current state of the art?
> 
> Is it feasible to store file in PostgreSQL?

Yes and no, it's another level of indirection, slower than pure
filesystem solution.

Rsync checks last read/access time, last write/modification time and
file hash before copying it. If no one of those metadata change, rsync
don't copy it. File hash must be recalculated if access time and
modification time change.

> Are there already projects which use PostgreSQL as storage backend?
> 
> I have the hope, that it would be easier to backup only the files
> which changed.

Rsync tries to backup only the files that changed. There are other
tools like cpdup, don't know if it's ported to linux, It's similar to
rsync. 

You can use a p2p system, unlike ftp, rsync, etc they store a full Tree Hash 
(Tiger Tree Hash often) of file content to allow multi peer to peer copy.

> Regards,
> Thomas Güttler
> 
> 
> Related question at rsnapshot mailing list:
> https://sourceforge.net/p/rsnapshot/mailman/rsnapshot-discuss/thread/57a1a2f3.5090...@thomas-guettler.de/
> -- 
> Thomas Guettler http://www.thomas-guettler.de/
> 

---   ---
Eduardo Morras 


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

[GENERAL] Bad query? Or planner?

[Devin Smith]

Hi,

I have recently started using postgres and have been operating under the
assumption that the query planner knows best (as long as I don't do
anything too stupid). I've been structuring my queries (and data) in a
certain way: writing re-usable subqueries, joining them in as necessary,
and only filtering the query at the top level (assuming that the query
planner will push down the appropriate restrictions as necessary). Of
course, also keeping in mind proper indexes to support efficient joins and
sorts.

I recently wrote a query that I thought was easy to reason about, and I
assumed the query planner would execute it efficiently.

SELECT * FROM xtag_stack_feed
JOIN (
  SELECT DISTINCT ON (do_post_xtag.xtag_ci) *
  FROM do_post_xtag
  JOIN do_post ON do_post.rc_id=do_post_xtag.post_rc_id
  ORDER BY do_post_xtag.xtag_ci ASC, do_post_xtag.post_rc_id DESC) last_post
ON last_post.xtag_ci=xtag_stack_feed.xtag_ci
ORDER BY decayed_to_base DESC
LIMIT 1;

Unfortunately, the query as written is not being executed efficiently. I
tried to rewrite it in a couple different ways without success, and then
learned about lateral joins. Rewritten as follows, it executes efficiently.

SELECT * FROM xtag_stack_feed
JOIN LATERAL (
  SELECT DISTINCT ON (do_post_xtag.xtag_ci) *
  FROM do_post_xtag
  JOIN do_post ON do_post.rc_id=do_post_xtag.post_rc_id
  WHERE do_post_xtag.xtag_ci=xtag_stack_feed.xtag_ci
  ORDER BY do_post_xtag.xtag_ci ASC, do_post_xtag.post_rc_id DESC) last_post
ON true
ORDER BY decayed_to_base DESC
LIMIT 1;

>From my naive perspective, it seems like the second query is semantically
equivalent to the first; it just has the join condition moved into the
subquery as a WHERE filter.

Am I doing something wrong? Is there room for query planner improvement in
cases like these?

I've attached the EXPLAIN ANALYZEs. Any help would be much appreciated!

Thanks,
-Devin
v3db=# SELECT version();
 version
--
 PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 
4.9.2, 64-bit
(1 row)

v3db=# EXPLAIN ANALYZE SELECT * FROM xtag_stack_feed
v3db-# JOIN (
v3db(#   SELECT DISTINCT ON (do_post_xtag.xtag_ci) *
v3db(#   FROM do_post_xtag
v3db(#   JOIN do_post ON do_post.rc_id=do_post_xtag.post_rc_id
v3db(#   ORDER BY do_post_xtag.xtag_ci ASC, do_post_xtag.post_rc_id DESC) 
last_post
v3db-# ON last_post.xtag_ci=xtag_stack_feed.xtag_ci
v3db-# ORDER BY decayed_to_base DESC
v3db-# LIMIT 1;

   QUERY PLAN
-
 Limit  (cost=61553.49..64173.98 rows=1 width=337) (actual 
time=5006.573..5006.574 rows=1 loops=1)
   ->  Nested Loop  (cost=61553.49..58453893.39 rows=22283 width=337) (actual 
time=5006.571..5006.571 rows=1 loops=1)
 Join Filter: (xtag_stack_feed.xtag_ci = last_post.xtag_ci)
 Rows Removed by Join Filter: 25973
 ->  Index Scan using xtag_stack_feed_decayed_to_base_xtag_ci_idx on 
xtag_stack_feed  (cost=0.41..5293.44 rows=48443 width=43) (actual 
time=0.088..0.088 rows=1 loops=1)
 ->  Materialize  (cost=61553.07..63843.13 rows=22283 width=294) 
(actual time=4041.034..4988.338 rows=25974 loops=1)
   ->  Subquery Scan on last_post  (cost=61553.07..62860.71 
rows=22283 width=294) (actual time=4041.025..4967.023 rows=25974 loops=1)
 ->  Unique  (cost=61553.07..62637.88 rows=22283 width=294) 
(actual time=4041.022..4960.130 rows=25974 loops=1)
   ->  Sort  (cost=61553.07..62095.48 rows=216962 
width=294) (actual time=4041.019..4837.741 rows=124511 loops=1)
 Sort Key: do_post_xtag.xtag_ci, 
do_post_xtag.post_rc_id COLLATE "C" DESC
 Sort Method: external merge  Disk: 67920kB
 ->  Hash Join  (cost=1591.71..12656.56 
rows=216962 width=294) (actual time=27.422..230.173 rows=216965 loops=1)
   Hash Cond: 
((do_post_xtag.post_rc_id)::bpchar = (do_post.rc_id)::bpchar)
   ->  Seq Scan on do_post_xtag  
(cost=0.00..4098.62 rows=216962 width=36) (actual time=0.007..29.305 
rows=216965 loops=1)
   ->  Hash  (cost=790.87..790.87 
rows=16787 width=258) (actual time=27.136..27.136 rows=16788 loops=1)
 Buckets: 16384  Batches: 2  Memory 
Usage: 2269kB
 ->  Seq Scan on do_post  
(cost=0.00..790.87 rows=16787 width=258) (actual time=0.004..5.972 rows=16788 
loops=1)
 Planning time: 3.842 ms
 Execution time: 5021.670 ms
(19 rows)

v3db=# EXPLAIN ANALYZE SELECT * FROM xtag_stack_feed

Re: [GENERAL] pg_dump system catalog

[Melvin Davidson]

On Mon, Nov 28, 2016 at 10:50 AM, Adrian Klaver 
wrote:

> On 11/28/2016 07:44 AM, Melvin Davidson wrote:
>
>>
>>
>
>>
>> *To clarify, you cannot dump the pg_catalog schema. It is the main
>> control of how all other objects are
>>
>
> Actually you can. I would not of thought so, but tried Achilleas's
> suggestion and it worked. Example:
>
> --
> -- Name: pg_aggregate; Type: TABLE; Schema: pg_catalog; Owner: postgres
> --
>
> CREATE TABLE pg_aggregate (
> aggfnoid regproc NOT NULL,
> aggkind "char" NOT NULL,
> aggnumdirectargs smallint NOT NULL,
> aggtransfn regproc NOT NULL,
> aggfinalfn regproc NOT NULL,
> aggmtransfn regproc NOT NULL,
> aggminvtransfn regproc NOT NULL,
> aggmfinalfn regproc NOT NULL,
> aggfinalextra boolean NOT NULL,
> aggmfinalextra boolean NOT NULL,
> aggsortop oid NOT NULL,
> aggtranstype oid NOT NULL,
> aggtransspace integer NOT NULL,
> aggmtranstype oid NOT NULL,
> aggmtransspace integer NOT NULL,
> agginitval text,
> aggminitval text
> );
>
> COPY pg_aggregate (aggfnoid 
>
>
> and you do get errors:
>
> pg_dump: WARNING: typtype of data type "any" appears to be invalid
> pg_dump: WARNING: typtype of data type "anyarray" appears to be invalid
> pg_dump: WARNING: typtype of data type "anyelement" appears to be invalid
> pg_dump: WARNING: typtype of data type "anyenum" appears to be invalid
> pg_dump: WARNING: typtype of data type "anynonarray" appears to be invalid
> pg_dump: WARNING: typtype of data type "anyrange" appears to be invalid
> pg_dump: WARNING: typtype of data type "cstring" appears to be invalid
>
> 
>
> Still not sure why you would want to, but you can.
>
>
> stored in the cluster. There is no point in dumping it and all it's
>> tables and views are already clearly
>> documented.
>> https://www.postgresql.org/docs/9.6/static/catalogs.html
>>
>> pg_catalog itself is generated with the initdb command when a new
>> postgresql cluster is generated.
>> https://www.postgresql.org/docs/9.6/static/creating-cluster.html
>> https://www.postgresql.org/docs/9.6/static/app-initdb.html*
>>
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize.  Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


*Hmmm, well you learn something new every day. Albeit, although you can
view the dump file, I'm pretty sure you cannot restore it to a database
because,*
*by definition, those tables would already exist in the database.*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Storing files: 2.3TBytes, 17M file count

[Daniel Verite]

Thomas Güttler wrote:

> Up to now we use rsync (via rsnapshot) to backup our data.
> 
> But it takes longer and longer for rsync to detect
> the changes. Rsync checks many files. But daily only
> very few files really change. More than 99.9% don't.

lsyncd+rsync has worked nicely for me on Linux in such cases,
as opposed to rsync alone which is indeed very slow with large
trees. Check out https://github.com/axkibe/lsyncd

If you think of using Postgres large objects, be aware that they
are stored in a single table (pg_largeobject), sliced
as rows of 1/4 block in size each (typically 2048 bytes).
2.3 TB in a single database would mean more than 1.2 billion
rows in that table, and as a system table it can't be partitioned
or moved to another tablespace.

OTOH with large objects, files can be stored and retrieved easily
between client and server with efficient built-in functions at both ends.
In particular, they don't need the binary<->text conversions or 
large memory allocations mentioned by Chris Travers upthread,
that may happen when writing your own methods with bytea columns.

But for the amount of data you have, the monolithic pg_largeobject
would likely be problematic.

Ideally there should be an extension implementing something like
DATALINK (SQL99), with external storage. I wonder if an extension
could provide custom WAL records replicating content changes to the
external storage of a standby. That would be awesome.


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite


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

Re: [GENERAL] pg_dump system catalog

[Adrian Klaver]

On 11/28/2016 07:44 AM, Melvin Davidson wrote:







*To clarify, you cannot dump the pg_catalog schema. It is the main
control of how all other objects are


Actually you can. I would not of thought so, but tried Achilleas's 
suggestion and it worked. Example:


--
-- Name: pg_aggregate; Type: TABLE; Schema: pg_catalog; Owner: postgres
--

CREATE TABLE pg_aggregate (
aggfnoid regproc NOT NULL,
aggkind "char" NOT NULL,
aggnumdirectargs smallint NOT NULL,
aggtransfn regproc NOT NULL,
aggfinalfn regproc NOT NULL,
aggmtransfn regproc NOT NULL,
aggminvtransfn regproc NOT NULL,
aggmfinalfn regproc NOT NULL,
aggfinalextra boolean NOT NULL,
aggmfinalextra boolean NOT NULL,
aggsortop oid NOT NULL,
aggtranstype oid NOT NULL,
aggtransspace integer NOT NULL,
aggmtranstype oid NOT NULL,
aggmtransspace integer NOT NULL,
agginitval text,
aggminitval text
);

COPY pg_aggregate (aggfnoid 


and you do get errors:

pg_dump: WARNING: typtype of data type "any" appears to be invalid
pg_dump: WARNING: typtype of data type "anyarray" appears to be invalid
pg_dump: WARNING: typtype of data type "anyelement" appears to be invalid
pg_dump: WARNING: typtype of data type "anyenum" appears to be invalid
pg_dump: WARNING: typtype of data type "anynonarray" appears to be invalid
pg_dump: WARNING: typtype of data type "anyrange" appears to be invalid
pg_dump: WARNING: typtype of data type "cstring" appears to be invalid



Still not sure why you would want to, but you can.



stored in the cluster. There is no point in dumping it and all it's
tables and views are already clearly
documented.
https://www.postgresql.org/docs/9.6/static/catalogs.html

pg_catalog itself is generated with the initdb command when a new
postgresql cluster is generated.
https://www.postgresql.org/docs/9.6/static/creating-cluster.html
https://www.postgresql.org/docs/9.6/static/app-initdb.html*

--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.



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


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

Re: [GENERAL] pg_dump system catalog

[Melvin Davidson]

On Mon, Nov 28, 2016 at 9:33 AM, Adrian Klaver 
wrote:

> On 11/28/2016 03:40 AM, Juliano wrote:
>
>> I'm not knew that system catalogs could be saved as schema, thanks
>> Achilleas.
>>
>> So, I would like to get the data and the schema of the system catalogs.
>> How can I include the data in a dump file with system schemas? (The
>> parameter -a makes sense together the parameter --schema?)
>>
>
> No.
>
> Use Achilleas's command and see what happens.
>
>
>
>
>> Regards,
>> Juliano
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>








*To clarify, you cannot dump the pg_catalog schema. It is the main control
of how all other objects are stored in the cluster. There is no point in
dumping it and all it's tables and views are already
clearlydocumented.https://www.postgresql.org/docs/9.6/static/catalogs.html
pg_catalog itself
is generated with the initdb command when a new postgresql cluster is
generated.https://www.postgresql.org/docs/9.6/static/creating-cluster.html
https://www.postgresql.org/docs/9.6/static/app-initdb.html
*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Storing files: 2.3TBytes, 17M file count

[Chris Travers]

On Mon, Nov 28, 2016 at 3:28 PM, Thomas Güttler <
guettl...@thomas-guettler.de> wrote:

> Hi,
>
> PostgreSQL is rock solid and one of the most reliable parts of our
> toolchain.
>
>Thank you
>
> Up to now, we don't store files in PostgreSQL.
>
> I was told, that you must not do this  But this was 20 years ago.
>
>
> I have 2.3TBytes of files. File count is 17M
>
> Up to now we use rsync (via rsnapshot) to backup our data.
>
> But it takes longer and longer for rsync to detect
> the changes. Rsync checks many files. But daily only
> very few files really change. More than 99.9% don't.
>
> Since we already store our structured data in postgres, I think
> about storing the files in PostgreSQL, too.
>
> What is the current state of the art?
>
> Is it feasible to store file in PostgreSQL?
>
> Are there already projects which use PostgreSQL as storage backend?
>
> I have the hope, that it would be easier to backup only the files which
> changed.
>


There is a tradeoff.  On one hand, as you note, it is easier to back things
up if you are storing the files in PostgreSQL.   Now, I have *not* looked
at how this would work for binary format transfer so that might be
different, but in most cases I have looked at, the downside is in the
encoding and decoding.

If files come in as hexadecimal, then you already have twice as much
transfer as space.  Then typically driver-based encoding will copy in the
process, meaning that you end up with many times the RAM used as the
files.  When I tested this in Perl, it was common for 8x the size of the
file to be used in RAM in the course of decoding and sending it on.
Driver, framework, and encoding may affect this, however.

Now, depending on what you are doing, that may not be a problem.  It sounds
like you have a large number of files, and they are up to a number MB in
size.  Since that memory usage would be short-term that may not be a
problem but I cannot say for you whether it is or not.

So be aware of the tradeoff and decide appropriately.

>
> Regards,
>Thomas Güttler
>
>
> Related question at rsnapshot mailing list:
> https://sourceforge.net/p/rsnapshot/mailman/rsnapshot-discus
> s/thread/57a1a2f3.5090...@thomas-guettler.de/
>
>
>
> --
> Thomas Guettler http://www.thomas-guettler.de/
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] Storing files: 2.3TBytes, 17M file count

[Adrian Klaver]

On 11/28/2016 06:28 AM, Thomas Güttler wrote:

Hi,

PostgreSQL is rock solid and one of the most reliable parts of our
toolchain.

   Thank you

Up to now, we don't store files in PostgreSQL.

I was told, that you must not do this  But this was 20 years ago.


I have 2.3TBytes of files. File count is 17M

Up to now we use rsync (via rsnapshot) to backup our data.

But it takes longer and longer for rsync to detect
the changes. Rsync checks many files. But daily only
very few files really change. More than 99.9% don't.


Are you rsyncing over all the files at one time?

Or do break it down into segments over the day?



Since we already store our structured data in postgres, I think
about storing the files in PostgreSQL, too.

What is the current state of the art?


I don't know.



Is it feasible to store file in PostgreSQL?


Yes, you can store a file in Postgres. Still I am not sure that stuffing 
17M files into Postgres is going to perform any better then dealing with 
them on the file system. In fact in Postgres they would be still be on 
the file system but with an extra layer above them.




Are there already projects which use PostgreSQL as storage backend?


The closest I remember is Bacula:

http://blog.bacula.org/documentation/documentation/

It uses a hybrid solution where the files are stored on a file server 
and data about the files is stored in a database. Postgres is one of the 
database backends it can work with.




I have the hope, that it would be easier to backup only the files which
changed.


Backup to where and how?
Are you thinking of using replication?



Regards,
   Thomas Güttler


Related question at rsnapshot mailing list:
https://sourceforge.net/p/rsnapshot/mailman/rsnapshot-discuss/thread/57a1a2f3.5090...@thomas-guettler.de/







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


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

Re: [GENERAL] pg_dump system catalog

[Adrian Klaver]

On 11/28/2016 03:40 AM, Juliano wrote:

I'm not knew that system catalogs could be saved as schema, thanks
Achilleas.

So, I would like to get the data and the schema of the system catalogs.
How can I include the data in a dump file with system schemas? (The
parameter -a makes sense together the parameter --schema?)


No.

Use Achilleas's command and see what happens.





Regards,
Juliano



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


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

[GENERAL] Storing files: 2.3TBytes, 17M file count

[Thomas Güttler]

Hi,

PostgreSQL is rock solid and one of the most reliable parts of our toolchain.

   Thank you

Up to now, we don't store files in PostgreSQL.

I was told, that you must not do this  But this was 20 years ago.


I have 2.3TBytes of files. File count is 17M

Up to now we use rsync (via rsnapshot) to backup our data.

But it takes longer and longer for rsync to detect
the changes. Rsync checks many files. But daily only
very few files really change. More than 99.9% don't.

Since we already store our structured data in postgres, I think
about storing the files in PostgreSQL, too.

What is the current state of the art?

Is it feasible to store file in PostgreSQL?

Are there already projects which use PostgreSQL as storage backend?

I have the hope, that it would be easier to backup only the files which changed.

Regards,
   Thomas Güttler


Related question at rsnapshot mailing list:
https://sourceforge.net/p/rsnapshot/mailman/rsnapshot-discuss/thread/57a1a2f3.5090...@thomas-guettler.de/



--
Thomas Guettler http://www.thomas-guettler.de/


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

Re: [GENERAL] pg_dump system catalog

[Juliano]

I'm not knew that system catalogs could be saved as schema, thanks Achilleas.

So, I would like to get the data and the schema of the system catalogs. How can 
I include the data in a dump file with system schemas? (The parameter -a makes 
sense together the parameter --schema?)

Regards,
Juliano