[GENERAL] unexpected pageaddr

Hi Setting up the hot_standby the only way I could it get the base remote cluster running was including pg_xlog Now using rsync as opposed to pg_basebackup seems ok aka psql postgres -c "select pg_start_backup('backup')" rsync -a /var/lib/pgsql/9.5/data

[GENERAL] hot standby questions

Hi Just trying to put together the hot_standby setup All docs I read are pointing to use as prefered method to use pg_basebackup to set the base So far so good But psql postgres -c "select pg_start_backup('backup')" pg_basebackup -D /var/lib/pgsql/sample -Ft -z -P psql postgres -c "select

Re: [GENERAL] Interesting streaming replication issue

OK this is reproducible now. 1. Stop a standby 2. Write some data to the master 3. Wait till the master has archived some WAL logs 4. Wait till the archived logs have been removed from pg_xlog 5. Start the standby. The standby will recover all logs from the master log archive up

Re: [GENERAL] select md5 result set

On Wed, Aug 2, 2017 at 4:50 PM, Peter Koukoulis wrote: > david, thanks for the help. > > Would this be the equivalent, for the statement in your email, for table > TEST1 (x integer, y varchar(20)): > > ft_node=# SELECT md5(string_agg(vals::text, '')) > ft_node-# from

Re: [GENERAL] Question about loading up a table

Hi I don't have an extra 4T of filespace. I could potentially move the attached lun from one server and attach to the other well that was my question how to check if its pg_dump thats bound. I have checked network performance - 9.8Gb and I can write more data to disk I do have 1 index A On

Re: [GENERAL] select md5 result set

david, thanks for the help. Would this be the equivalent, for the statement in your email, for table TEST1 (x integer, y varchar(20)): ft_node=# SELECT md5(string_agg(vals::text, '')) ft_node-# from (select x,y from test1) vals(x,y); ? Peter On Thu, 3 Aug 2017 at 00:25 David G. Johnston

Re: [GENERAL] select md5 result set

On Wed, Aug 2, 2017 at 3:42 PM, Peter Koukoulis wrote: > > SQL> select dbms_sqlhash.gethash('select x,y from test1',2) as md5_value > from dual; > > MD5_VALUE > > >

[GENERAL] PostgreSQL 9.3 Uninstall error - Windows 2012

Hi, I am trying to uninstall PostgreSQL 9.3 from a Windows 2012 server using the *uninstall-postgresql.exe*. During the uninstall, I get an error: *The uninstall-postgresql.dat file cannot be found and is required to uninstall the application, aborting. Uninstallation did not complete

[GENERAL] select md5 result set

Hi I'm attempting to emulate feature available in Oracle, namely dbs_sqlhash. For example, given the following table data values: SQL> select x,y from test1; X Y -- 5 White 1 YYY 2 Goodbye 6 Black I can create a single hash value over the entire result set,

Re: [GENERAL] Fwd: Planner oversight for GIN indices?

Kurt Kartaltepe writes: > ... I feel like this is potentially a question more for pgsql-hackers but > the mailing list suggests asking elsewhere before posting there and > this wasnt quite a "bug". A quick uninformed peek at the planner code > makes me think this isn't

Re: [GENERAL] bidirectional mapping?

On 08/02/2017 01:35 PM, John McKown wrote: On Wed, Aug 2, 2017 at 10:55 AM, Chris Travers >wrote: On Wed, Aug 2, 2017 at 5:44 PM, John McKown > wrote:

Re: [GENERAL] bidirectional mapping?

On Wed, Aug 2, 2017 at 10:55 AM, Chris Travers wrote: > > > On Wed, Aug 2, 2017 at 5:44 PM, John McKown > wrote: > >> Is there a simple way to do bidirectional mapping of a table with itself? >> I am thinking of a "spousal" type

Re: [GENERAL] Would you add a --dry-run to pg_restore?

On Wed, Aug 02, 2017 at 12:10:37PM -0500, Edmundo Robles wrote: > I imagine pg_restore can execute the instructions on dump but don't > write on disk. just like David said: "tell me what is going to happen > but don't actually do it" In fact, this already exists: pg_restore

Re: [GENERAL] Would you add a --dry-run to pg_restore?

On Wed, Aug 2, 2017 at 10:10 AM, Edmundo Robles wrote: > I imagine pg_restore can execute the instructions on dump but don't > write on disk. just like David said: "tell me what is going to happen > but don't actually do it" > You may wish to respond to the actual

Re: [GENERAL] Would you add a --dry-run to pg_restore?

I imagine pg_restore can execute the instructions on dump but don't write on disk. just like David said: "tell me what is going to happen but don't actually do it" Regards. On Wed, Aug 2, 2017 at 11:49 AM, Tom Lane wrote: > Edmundo Robles

Re: [GENERAL] Would you add a --dry-run to pg_restore?

> On Aug 2, 2017, at 9:02 AM, Edmundo Robles wrote: > > I mean, to verify the integrity of backup i do: > > gunzip -c backup_yesterday.gz | pg_restore -d my_database && echo > "backup_yesterday is OK" > > but my_database's size, uncompresed, is too big more than

Re: [GENERAL] Would you add a --dry-run to pg_restore?

Edmundo Robles writes: > I mean, to verify the integrity of backup i do: > gunzip -c backup_yesterday.gz | pg_restore -d my_database && echo > "backup_yesterday is OK" > but my_database's size, uncompresed, is too big more than 15G and > sometimes i have no

Re: [GENERAL] Do not INSERT if UPDATE fails

On Wed, Aug 2, 2017 at 8:58 AM, Alexander Farber wrote: > However if the user record is not found or the user already has vip_until > >= CURRENT_TIMESTAMP (i.e. the user has already purchased "vip status") I > would like to cancel the INSERT. > > ​You can "join" two

Re: [GENERAL] Would you add a --dry-run to pg_restore?

On Wed, Aug 2, 2017 at 9:02 AM, Edmundo Robles wrote: > Will be great to have a dry run option, because the time to verify > reduces a lot and will save space on disk, because just execute with no > write to disk. > "Dry run", the way I understand it, can be

Re: [GENERAL] Do not INSERT if UPDATE fails

Does insert's "on conflict" clause not work for this usage? -- 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] Question about loading up a table

On Tue, Aug 1, 2017 at 4:27 PM, Alex Samad wrote: > Hi > > So just to go over what i have > > > server A (this is the original pgsql server 9.2) > > Server X and Server Y ... PGSQL 9.6 in a cluster - streaming replication > with hot standby. > > > I have 2 tables about 2.5T of

[GENERAL] Would you add a --dry-run to pg_restore?

I mean, to verify the integrity of backup i do: gunzip -c backup_yesterday.gz | pg_restore -d my_database && echo "backup_yesterday is OK" but my_database's size, uncompresed, is too big more than 15G and sometimes i have no space to restore it, so always i must declutter my disk

[GENERAL] Do not INSERT if UPDATE fails

Good evening, I have a custom SQL function in PostgreSQL 9.5.7 which adds a "log entry" to the table words_payments and then updates "vip_until" column in another table: CREATE OR REPLACE FUNCTION words_buy_vip( in_sid text, in_social integer, in_tid text, in_item

Re: [GENERAL] bidirectional mapping?

On Wed, Aug 2, 2017 at 5:44 PM, John McKown wrote: > Is there a simple way to do bidirectional mapping of a table with itself? > I am thinking of a "spousal" type relationship, where it is true that if A > is spouse of B, then B is spouse of A. I don't necessarily

[GENERAL] bidirectional mapping?

Is there a simple way to do bidirectional mapping of a table with itself? I am thinking of a "spousal" type relationship, where it is true that if A is spouse of B, then B is spouse of A. I don't necessarily want "A" and "B" to be a monogamous relationship because that is not always be true world

Re: [GENERAL] org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command

On Wed, Aug 2, 2017 at 9:42 AM, Emi wrote: > Hello, > > Running psql table updates() by using > org.springframework.scheduling.quartz.JobDetailFactoryBean > cronjob from web application. Got the following exception: > > org.postgresql.util.PSQLException: FATAL:

Re: [GENERAL] org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command

Emi wrote: > Running psql table updates() by using > org.springframework.scheduling.quartz.JobDetailFactoryBean cronjob from > web application. Got the following exception: > > org.postgresql.util.PSQLException: FATAL: terminating connection due to > administrator command > > > Re-run the same

[GENERAL] org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command

Hello, Running psql table updates() by using org.springframework.scheduling.quartz.JobDetailFactoryBean cronjob from web application. Got the following exception: org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command Re-run the same cronjob several