Postgres - search for value throughout many tables?

[czezz]

Hi everyone,  I want to aks if anyone knows is there a way to search for 
specific "value" throughout list of tables OR all tables in databse?   
Cheers,  czezz

Re: Postgres - search for value throughout many tables?

[David G. Johnston]

On Wednesday, August 8, 2018, czezz  wrote:

> Hi everyone,
> I want to aks if anyone knows is there a way to search for specific
> "value" throughout list of tables OR all tables in databse?
>

Can you pg_dump your database to plain text and search that?  Nothing built
in provides that ability though you possibly could work up something using
dynamic sql.

David J.

FW: Pg_rewind cannot load history wal

[Richard Schmidt]

We think we have found our missing step. We needed to do an ordered shutdown of 
the original primary before promoting the standby
I.e.

>1. Make some changes to the A (the primary) and check that they are replicated 
>to the B (the standby)

Missing step:
 Perform ordered shutdown of A (the primary)

>2.Promote B to be the new primary

This know means that we have this simple use-case working.




This email and any attachments may contain confidential information. If you are 
not the intended recipient, your use or communication of the information is 
strictly prohibited. If you have received this message in error please notify 
MetService immediately.

Re: Postgres - search for value throughout many tables?

[Achilleas Mantzios]

If the num of tables is smallish you could run smth like

select * from (
select table1::text as thecol from table1
UNION select table2::text FROM table2
UNION select table3::text FROM table3
UNION .
) as qry WHERE thecol ~* 'some pattern';

On 08/08/2018 17:09, czezz wrote:

Hi everyone,
I want to aks if anyone knows is there a way to search for specific "value" 
throughout list of tables OR all tables in databse?

Cheers,
czezz



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

Odp: Re: Postgres - search for value throughout many tables?

[czezz]

Hi, thanks that is actually a good hint :)




  Dnia 8 sierpnia 2018 16:13 David G. Johnston 
 napisał(a):


   On Wednesday, August 8, 2018, czezz <   cz...@o2.pl > 
wrote:  Hi everyone,  I want to aks if anyone knows is there a way to search 
for specific "value" throughout list of tables OR all tables in 
databse?  Can you pg_dump your database to plain text and search that?  Nothing 
built in provides that ability though you possibly could work up something 
using dynamic sql.  David J.

Postgres replication -- .done file in slave

[Vijay Rajah]

Hello Everyone,

I have a postgres 9.5 with Master slave setup. The slave is a streaming 
replication client.


I have a lot ( >100k ) of .done files in my slave's 
pg_xlog/archive_status/ directory


Since this is a streaming replication I have disabled archive_mode in 
both master & slave. Also, I use barman for backup, which also uses 
streaming backup for backups.


Here is my relevant part of postgresql .conf file

   |wal_level =hot_standby archive_timeout =60archive_mode =off#We dont
   need this aswe will be usingbarman streaming max_wal_senders
   =5wal_keep_segments =10wal_log_hints =onhot_standby
   =onmax_replication_slots =5synchronous_commit =local|

Also here is what I have in recovery.conf

   |standby_mode =onprimary_conninfo ='host=10.xx.xxx.xx port=5432
   user=replication password=**
   application_name=salve_syncstdby'trigger_file
   ='//make_me_master'archive_cleanup_command
   ='/usr/pgsql-9.5/bin/pg_archivecleanup 
   %r'primary_slot_name ='slave_repl'recovery_target_timeline =latest
   restore_command ='/usr/bin/barman-wal-restore -p 4 -s  -U
   barman  %f %p'|

Is it ok to clear the .done files?


-Thanks in advance

Vijay


PS: I have posted the same question in 
https://stackoverflow.com/questions/51725092/postgres-replication-done-file-in-slave

Re: Postgres - search for value throughout many tables?

[Christopher Browne]

On Wed, 8 Aug 2018 at 10:14, David G. Johnston
 wrote:
>
> On Wednesday, August 8, 2018, czezz  wrote:
>>
>> Hi everyone,
>> I want to aks if anyone knows is there a way to search for specific "value" 
>> throughout list of tables OR all tables in databse?
>
>
> Can you pg_dump your database to plain text and search that?  Nothing built 
> in provides that ability though you possibly could work up something using 
> dynamic sql.

If there are some tables that are extraordinarily large that would not
be good candidates, this could be excessively expensive.

If you can identify a specific set of tables that are good candidates,
then a faster option might involve:
  pg_dump --data-only --table=this_table --table=that_table
--table=other_table databaseURI

or, if there are only a few tables to omit...
  pg_dump --data-only --exclude-table=this_irrelevant_big_table
--exclude-table=another_big_irrelevant_table databaseURI

-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

Re: Pg_rewind cannot load history wal

[Abhinav Mehta]

Yes, consider using Repmgr.

> On 08-Aug-2018, at 3:20 AM, Richard Schmidt  
> wrote:
> 
> We think we have found our missing step. We needed to do an ordered shutdown 
> of the original primary before promoting the standby
> I.e.
>  
> >1. Make some changes to the A (the primary) and check that they are 
> >replicated to the B (the standby)
>  
> Missing step:
>  Perform ordered shutdown of A (the primary)
>  
> >2.Promote B to be the new primary
>  
> This know means that we have this simple use-case working. 
>  
> 
> 
> This email and any attachments may contain confidential information. If you 
> are not the intended recipient, your use or communication of the information 
> is strictly prohibited. If you have received this message in error please 
> notify MetService immediately.
>