Re: [GENERAL] Associating a basebackup and it's .backup WAL file
That is really unfortunate. It seems it would be a nice feature for pg_basebackup to simply create a .metadata file in basebackup output directory or something along those lines. Non tarballed/compressed basebackup is fine since I can read the label, but most people probably want to always compress. I'll probably try to get the WAL file by getting last modified .backup right after basebackup invoke. I also found another "automation" problem looking at PITR recovery documentation, specifically: "If you have unarchived WAL segment files that you saved in step 2, copy them into pg_xlog/. (It is best to copy them, not move them, so you still have the unmodified files if a problem occurs and you have to start over.)" Seems like a lot of manual work to me, to automate it I'd basically have to diff both directories and then copy only the newest differences over to the recovery. So far I was unable to find a supersecret git repo with bash scripts accomplishing this tasks which is surprising. On 07/13/2017 11:26 AM, Michael Paquier wrote: On Thu, Jul 13, 2017 at 10:30 AM, cen <imba...@gmail.com> wrote: Given a basebackup base.tar.gz and an archive of WAL files, is there any way to find out which .backup WAL file is associated with the basebackup from command line? Not from what Postgres ships directly. Without any custom meta data save with each one of your backups, say something that you write after calling pg_basebackup, you would need to untar base.tar to look for the backup_label file. My use case is for a retention policy bash script which: -deletes all basebackups older than X days -runs pg_archivecleanup for the oldest basebackup I just don't know how to find out which WAL to feed to pg_archivecleanup at this point. Recalling something I know about, pg_rman uses its own meta data to do this decision making with dedicated folder names that use a structure and names based on timestamps, and this meta data is written and saved when each backup is taken. This saves future lookups at all tarballs when doing cleanup of past backups. I am not sure about the more popular barman and pgBackrest since I know them less, but I would imagine they handle retention policies similarly.
[GENERAL] Associating a basebackup and it's .backup WAL file
Hi Given a basebackup base.tar.gz and an archive of WAL files, is there any way to find out which .backup WAL file is associated with the basebackup from command line? My use case is for a retention policy bash script which: -deletes all basebackups older than X days -runs pg_archivecleanup for the oldest basebackup I just don't know how to find out which WAL to feed to pg_archivecleanup at this point. -- 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] Weird periodical pg log
Hmm indeed that seems to be the case. Thanks for the tip! On 05/19/2017 04:10 PM, John R Pierce wrote: On 5/19/2017 1:25 AM, cen wrote: < 2017-05-15 17:00:41.861 CEST >LOG: parameter "archive_command" changed to ""/opt/omni/lbin/pgsqlbar.exe" -stage %p -backup" I believe /opt/omni is the default installation path for HP Data Protector, formerly known as OmniBack. That comprehensive backup system includes database backup capabilities, I'm guessing thats what you're seeing here. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Weird periodical pg log
Every single day at exactly the same hour I get this in my pg_log: < 2017-05-15 17:00:29.517 CEST >FATAL: pg_hba.conf rejects connection for host "...", user "postgres", database "template1", SSL off < 2017-05-15 17:00:29.571 CEST >WARNING: archive_mode enabled, yet archive_command is not set < 2017-05-15 17:00:41.859 CEST >LOG: received SIGHUP, reloading configuration files < 2017-05-15 17:00:41.861 CEST >LOG: parameter "archive_command" changed to ""/opt/omni/lbin/pgsqlbar.exe" -stage %p -backup" < 2017-05-15 17:00:51.865 CEST >LOG: received SIGHUP, reloading configuration files < 2017-05-15 17:00:51.867 CEST >LOG: parameter "archive_command" removed from configuration file, reset to default Postgres 9.5 is running on Centos 7. I checked all cron jobs and scripts, there is nothing external that would cause this so my guess is that this is internal Postgres cron job tryin to do "something", I just don't know what exactly. We have WAL replication set up, these logs appear on master. Perhaps we have something off in our conf? Best regards, cen -- 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] Conferences for a DBA?
How about PGCon? I've never been but the online videos are always interesting. Nathan Stocks je 28. 02. 2017 ob 00:25 napisal: What worthwhile conferences should a PostgreSQL DBA consider going to? There have been some good sessions at OSCON in the past, but I was wondering about more DBA-specific events. ~ Nathan
[GENERAL] FTS prefix search - put full hits first
Hi
I have this FTS query:
SELECT * FROM receivers r WHERE r.tsv @@ unaccent('john:*')::tsquery
ORDER BY ts_rank(r.tsv, unaccent('john:*')::tsquery) DESC;
Is there any way to tell FTS to put records with "John" first and others
after that (Johhny, Johnson etc)?
Basically, I want to have full word hits on top.
Best regards, Klemen
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full text search randomly not working for short prefixes?
Thanks, that makes sense. I think I'll go with the cast approach, I
don't really need stemming anywhere.
Tom Lane je 02. 12. 2016 ob 16:33 napisal:
cen <imba...@gmail.com> writes:
Something funny going on with my full text search.. and I have no idea what.
The way to debug this sort of thing is generally to look at what tsquery
you're actually getting. I get
regression=# select to_tsquery(unaccent('a:*'));
NOTICE: text-search query contains only stop words or doesn't contain lexemes,
ignored
to_tsquery
(1 row)
regression=# select to_tsquery(unaccent('an:*'));
NOTICE: text-search query contains only stop words or doesn't contain lexemes,
ignored
to_tsquery
(1 row)
regression=# select to_tsquery(unaccent('ana:*'));
to_tsquery
'ana':*
(1 row)
Of course, only the last is going to match 'ana'.
So you need to use a text search configuration in which a/an are
not stop words. Or possibly you could cast the unaccent result
directly to tsquery rather than passing it through to_tsquery(),
though likely that would just have a different set of failure modes
with queries where you do wish stemming would occur.
The problem with "no" seems to be the same.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Full text search randomly not working for short prefixes?
Something funny going on with my full text search.. and I have no idea what.
I have a receiver called "Ana", this is her tsv column:
'3865100':4 'acevent...@mailinator.com':3B 'ana':1A 'novak':2A
This queries do not find her:
SELECT * FROM receivers r WHERE r.tsv @@ to_tsquery(unaccent('a:*'));
SELECT * FROM receivers r WHERE r.tsv @@ to_tsquery(unaccent('an:*'));
This does:
SELECT * FROM receivers r WHERE r.tsv @@ to_tsquery(unaccent('ana:*'));
Now to an even more interesting part: I have 3 people with last name
"Novak" and one with name "Nov"
This query finds all 4:
SELECT * FROM receivers r WHERE r.tsv @@ to_tsquery(unaccent('n:*'));
This finds NONE:
SELECT * FROM receivers r WHERE r.tsv @@ to_tsquery(unaccent('no:*'));
This finds all 4 again:
SELECT * FROM receivers r WHERE r.tsv @@ to_tsquery(unaccent('nov:*'));
..and this finds all with the last name only:
SELECT * FROM receivers r WHERE r.tsv @@ to_tsquery(unaccent('nova:*'));
These are the TSV columns of last name:
"'3865100':4 'janez':1A 'janezno...@mailinator.com':3B 'novak':2A"
"'3865100':4 'acevent...@mailinator.com':3B 'ana':1A 'novak':2A"
"'3865100':4 'novak':2A 'tine':1A 'tno...@mailinator.com':3B"
"'2141500':4 'alen.n...@gmailer.com':3B 'allan':1A 'novak':2A"
And the first name:
"'38651604724':6 'brez':3A 'list':4A 'nov':1A
'novreceiver...@mailinator.com':5B 'receiv':2A"
What is going on here?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Full text search tsv column aproach vs concat confusion
Hi I am seeking some clarification in regard to full text search across multiple tables and what the best approach is. Documentation talks about two approaches when it comes to building a document: on-the-fly concat of columns and a dedicated tsv column approach. Let's say I want to perform a search for |"txt1 & txt2 & txt3" on columns table1.col1, table1.col2 and table2.col1. I see the following solutions:| |1. Concat all three into a document and perform a FTS.| |SELECT * FROM ( | |SELECTto_tsvector(table1.col1)||to_tsvector(table1.col2)||to_tsvector(table2.col1)asdocument F|||ROM table1 LEFTJOINtable2 ONtable1.table2_id=table2.id| ) subquery |||WHEREsubquery.document@@to_tsquery(unaccent(?));| | |2. Create a tsv column in each table, concat tsv columns and perform FTS on that.| |SELECT*FROMtable1 LEFTJOINtable2 ONtable1.table2_id=table2.id WHEREtable1.tsv ||tale2.tsv @@to_tsquery(unaccent(?));| |3. Have a tsv column only in table1 and insert table2.col1 to the tsv via triggers. Works but seems very hacky.| | | |It seems to me that option #2 is fast and easy to implement but I am not sure what the concat of tsvs really means from index usage and performance standpoint. Option #1 is the most flexible and I'd use that all the time if it was not THAT much slower than tsv column approacj. Documentation on TSV columns states: "||Another advantage is that searches will be faster, since it will not be necessary to redo the to_tsvector calls to verify index matches." | The question is, how much faster are tsv columns really? Are there any benchmarks about this? If the performance difference is negligible I'd advocate that using tsv columns is a waste of time and space in most general cases. But since there is no information on how much faster it's hard to decide. Best regards, Klemen ||
