[GENERAL] unexpected pageaddr

[armand pirvu]

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  postgres@192.168.5.252:/var/lib/pgsql/9.5
psql postgres -c "select pg_stop_backup()"

But

cp: cannot stat ‘/var/lib/pgsql/pg_xlog_archive/00010078’: No 
such file or directory
< 2017-08-02 22:30:40.949 CDT >LOG:  unexpected pageaddr 0/3D00 in log 
segment 00010078, offset 0

and later


cp: cannot stat ‘/var/lib/pgsql/pg_xlog_archive/00010079’: No 
such file or directory
< 2017-08-02 22:30:40.949 CDT >LOG:  unexpected pageaddr 0/3D00 in log 
segment 00010079, offset 0
cp: cannot stat ‘/var/lib/pgsql/pg_xlog_archive/00010079’: No 
such file or directory

Things seem okay but the unexpected pageaddr  error is a bit unsettling .

Any thougts ? Something to be really worried about ? Did I miss it again ? :))


Thanks
Armand



-- 
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 questions

[armand pirvu]

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 pg_stop_backup()"


will get me on target
< 2017-08-02 22:09:45.348 CDT >LOG:  database system was interrupted; last 
known up at 2017-08-02 22:06:28 CDT
< 2017-08-02 22:09:45.353 CDT >LOG:  entering standby mode
< 2017-08-02 22:09:45.353 CDT >LOG:  invalid primary checkpoint record
< 2017-08-02 22:09:45.353 CDT >LOG:  invalid secondary checkpoint record
< 2017-08-02 22:09:45.353 CDT >PANIC:  could not locate a valid checkpoint 
record
< 2017-08-02 22:09:45.523 CDT >LOG:  startup process (PID 10895) was terminated 
by signal 6: Aborted
< 2017-08-02 22:09:45.523 CDT >LOG:  aborting startup due to startup process 
failure


where as
psql postgres -c "select pg_start_backup('backup')"
pg_basebackup -D /var/lib/pgsql/sample  -Ft -z -P -x
psql postgres -c "select pg_stop_backup()"

I am good to go

Pretty much every where I looked at -x is not mentioned to be used

So what gives ? What did I miss ? It's gotta be soomething

Thanks
Armand



-- 
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] Interesting streaming replication issue

[James Sewell]

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 to log X,
it will then try to get log X+1 and fail (doesn't exist).

It will then try to start streaming log X (not X+1) from the master and
fail (it's been archived). This will loop forever, example below.

scp: /archive/xlog//000D.history: No such file or directory
2017-08-03 10:26:41 AEST [578]: [1037-1] user=,db=,client=  (0:0)LOG:
 restored log file "000C006 E00AE" from archive
scp: /archive/xlog//000C006E00AF: No such file or directory
2017-08-03 10:26:41 AEST [68161]: [1-1] user=,db=,client=  (0:0)LOG:
 started streaming WAL from primary  at 6E/AE00 on timeline 12
2017-08-03 10:26:41 AEST [68161]: [2-1] user=,db=,client=  (0:XX000)FATAL:
 could not receive data from WAL s tream: ERROR:  requested WAL segment
000C006E00AE has already been removed



At this stage the standby has log X in pg_xlog, and this log has an
identical md5 checksum to the log in the master archive.

Performing a pg_switch_xlog on the master pushes log X+1 to the archive,
which is picked up by the standby allowing streaming replication to start.

The only interesting thing I can see in log X is that it's 99% made up
of FPI_FOR_HINT records.

Any ideas?

Cheers,
James





James Sewell,
PostgreSQL Team Lead / Solutions Architect



Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009
*P *(+61) 2 8099 9000 <(+61)%202%208099%209000>  *W* www.jirotech.com  *F *
(+61) 2 8099 9099 <(+61)%202%208099%209000>

On Fri, Jul 28, 2017 at 6:28 AM, James Sewell 
wrote:

>
>>>
 are you sure you're scp'ing from the archive, not from pg_xlog?

>>>
>>> Yes:
>>>
>>> restore_command = 'scp -o StrictHostKeyChecking=no 
>>> 10.154.19.30:/archive/xlog//%f
>>> %p'
>>>
>>> Although you are right - that would almost make sense if I had done that!
>>>
>>
>> Sounds a lot like a cleanup process on your archive directory or
>> something getting in the way. Are the logs pg is asking for in that archive
>> dir?
>>
>
> That's the strange thing - if you look at the log not only are they there,
> the standby has already retrieved them.
>
> It's then asking for the log again via the stream.
> --
> James Sewell,
> PostgreSQL Team Lead / Solutions Architect
>
>
>
> Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009
> *P *(+61) 2 8099 9000  *W* www.jirotech.com  *F *(+61) 2 8099 9099
>

-- 

--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [GENERAL] select md5 result set

[David G. Johnston]

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 (select x,y from test1) vals(x,y);
> ?
>
>
​The subquery is redundant if you already have a table:

select md5(string_agg(test1::text, '')) from test1;

David J.​

Re: [GENERAL] Question about loading up a table

[Alex Samad]

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 3 August 2017 at 02:11, Scott Marlowe  wrote:

> 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 diskspace.
> >
> > I want to get the date from A into X and X will replicate into Y.
> >
> >
> > I am currently on X using this command
> >
> > pg_dump -U  -h  -t BIGTABLE -a  | sudo -u postgres
> -i
> > psql -q ;
> >
> > This is taking a long time, its been 2 days and I have xfered around 2T..
> > This is just a test to see how long and to populate my new UAT env. so I
> > will have to do it again.
> >
> > Problem is time.  the pg_dump process is single threaded.
> > I have 2 routers in between A and X but its 10G networking - but my
> network
> > graphs don't show much traffic.
> >
> > Server X is still in use, there are still records being inserted into the
> > tables.
> >
> > How can I make this faster.
> >
> > I could shutdown server A and present the disks to server X, could I load
> > this up in PGSQL and do a table to table copy - i presume this would be
> > faster ... is this possible ?  how do I get around the same DB name ?
> > What other solutions do I have ?
>
> Yes, but if it's taking days to transfer 2TB then you need to
> investigate where your performance is tanking.
>
> Have you tried resyncing / scping files across the network to see how
> fast your network connection is?
>
> Have you tried just pg_dumping / restoring locally to get an idea how
> fast you can dump / restore withoout doing it over a network
> connection?
>
> Are you IO bound? Network bound? CPU bound?
>
> Is the destination copying data, or building indexes? Do you insert
> into a schema that already has indexes in place? If so have you tried
> dropping the indexes first and rebuilding them?
>

Re: [GENERAL] select md5 result set

[Peter Koukoulis]

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 
wrote:

> 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
>>
>> 
>> 9FDA7FA725B783172CA371DA04AD5754
>>
>>
>> Can I do something similar in PostgreSQL ?
>>
>>
> ​Similar.​
>
>  SELECT md5(string_agg(vals::text, ''))
>  FROM ( VALUES (1, 2), (2, 3) ) vals (x, y)
>
> ​David J.
>
>

Re: [GENERAL] select md5 result set

[David G. Johnston]

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
> 
> 
> 9FDA7FA725B783172CA371DA04AD5754
>
>
> Can I do something similar in PostgreSQL ?
>
>
​Similar.​

 SELECT md5(string_agg(vals::text, ''))
 FROM ( VALUES (1, 2), (2, 3) ) vals (x, y)

​David J.

[GENERAL] PostgreSQL 9.3 Uninstall error - Windows 2012

[Pankaj Arora]

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
successfully.*

The uninstall-postgresql.dat does exist in the install folder. Not sure
what the dat file contains and if this file is corrupt on my system.

Questions:
1. Can someone give me info on the uninstall-postgresql.dat file and what
it contains?
2. Is there anyway to get rid of this error message and still use the
uninstall-postgresql.exe to uninstall the DB from the Windows system?

Thanks

-- 
~*Pankaj Arora*

[GENERAL] select md5 result set

[Peter Koukoulis]

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, specifically
md5, in a  query as follows:

SQL> select dbms_sqlhash.gethash('select x,y from test1',2) as md5_value
from dual;

MD5_VALUE

9FDA7FA725B783172CA371DA04AD5754


Can I do something similar in PostgreSQL ?

Thanks
P

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

[Tom Lane]

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 exactly trivial but from the "simplicity" of
> the workaround id hope it is possible. This seems like an issue that
> would affect all inverse indices or more generally any index where
> multiple clauses against the same index might have different
> performance characteristics that could be determined at plan time (so
> only for constant restrictions).

Yeah, the planner's traditional behavior here is just to throw every
potentially indexable clause into the indexqual list and let the index AM
sort things out at runtime.  This is demonstrably the best thing to do
for btree, where the AM itself can identify redundant or contradictory
scan clauses with 100% accuracy at scan start.  I think that GIN might
be the only case where including seriously-unselective quals is really
a big loser --- the cause of that being that it might have to iterate
over some very long posting lists.  We did some work recently to make
GIN smarter about combinations of long and short posting lists, but it
seems that it's still not smart enough to cover this case completely.

I'm not sure offhand whether it's better to make the planner try to
identify indexable clauses it should not hand to the index AM, or to
insist that the index AM ought to be smart enough to cope.  The difficulty
with the former approach is that the planner can't be counted on to do the
right thing if there are non-constant comparison values, plus if there are
a lot of potentially-indexable clauses it's not cheap to consider all
combinations.  The difficulty with the latter approach is that the index
AM might not have the necessary information either.  If the best it can do
is to look into the planner's statistics, that feels a bit duplicative.
(But it might still be a win, because we'd definitely have the actual
comparison value at runtime.)

Anyway, sorry, this is a research problem rather than something that's
easy to fix.

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

Re: [GENERAL] bidirectional mapping?

[Rob Sargent]

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:

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 wide.
The best I can come up with so far is something like:

CREATE TABLE forespouse (PERSON integer PRIMARY KEY,
SPOUSE integer UNIQUE
CHECK( PERSON != SPOUSE) -- sorry, can't marry self
);
CREATE UNIQUE INDEX ON forespouse(PERSON, SPOUSE);
CREATE UNIQUE INDEX ON forespouse(SPOUSE, PERSON);
-- I'm not sure that the above indices are needed.
CREATE VIEW backspouse AS SELECT SPOUSE, PERSON FROM forespouse;
CREATE VIEW spouse AS
 SELECT PERSON, SPOUSE FROM forespouse
 UNION
 SELECT SPOUSE, PERSON FROM backspouse
;


Usually the way I have done this is to normalise the
representation and use a table method for converting for joins. 
In other words:


create table marriage (party integer primary key, counterparty
integer unique, check party < counterparty);

Not sure I agree with the uniqueness of the parties involved. Unique on 
(party, counterparty) isn't a for sure, if there's any temporal 
dimension involved, in which case I would prefer (id, party, counterparty).

Re: [GENERAL] bidirectional mapping?

[John McKown]

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 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
>> wide. The best I can come up with so far is something like:
>>
>> CREATE TABLE forespouse (PERSON integer PRIMARY KEY,
>> SPOUSE integer UNIQUE
>> CHECK( PERSON != SPOUSE) -- sorry, can't marry self
>> );
>> CREATE UNIQUE INDEX ON forespouse(PERSON, SPOUSE);
>> CREATE UNIQUE INDEX ON forespouse(SPOUSE, PERSON);
>> -- I'm not sure that the above indices are needed.
>>
>> CREATE VIEW backspouse AS SELECT SPOUSE, PERSON FROM forespouse;
>> CREATE VIEW spouse AS
>>  SELECT PERSON, SPOUSE FROM forespouse
>>  UNION
>>  SELECT SPOUSE, PERSON FROM backspouse
>> ;
>>
>
> Usually the way I have done this is to normalise the representation and
> use a table method for converting for joins.  In other words:
>
> create table marriage (party integer primary key, counterparty integer
> unique, check party < counterparty);
>

​I _knew_ there must be a better way. I just didn't see it. Many thanks!​



>
> This way you can ensure that each relationship is only recorded once.
>
> Then I would create a function that returns an array of the parties.
>
> CREATE OR REPLACE FUNCTION parties(marriage) returns int[] language sql as
> $$
> select array[$1.party, $1.counterparty];
> $$;
>
> Then you can create a gin index:
>

​I need to become familiar with "gin" indices, I guess. I'm a bit behind in
my knowledge of PostgreSQL. I also try to use "plain old SQL" as defined in
the "standard". Mainly because I use both PostgreSQL and SQLite.​



>
> create index marriage_parties_idx on marriage using gin(parties(marriage));
>
> Then you can query on:
> select ... from people p1 where first_name = 'Ashley'
>  join marriage m on p1 = any(marriage.parties)
>  join people p2 on p2 = any(marriage.parties) and p2.id <> p1.id
>
>>
>>
> --
> Best Wishes,
> Chris Travers
>
> Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
> lock-in.
> http://www.efficito.com/learn_more
>



-- 
Veni, Vidi, VISA: I came, I saw, I did a little shopping.

Maranatha! <><
John McKown

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

[Karsten Hilbert]

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 --file=commands.sql your-backup

Then read commands.sql.

It will tell you what is going to happen but not actually do it.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] Would you add a --dry-run to pg_restore?

[David G. Johnston]

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 points being made as to why
separating out "writing" from "execution" doesn't provide meaningful value
- especially not for the effort it would take.

David J.

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

[Edmundo Robles]

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  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 space  to restore it, so always i must declutter
> my
> >  disk first.
>
> > 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.
>
> What do you imagine a dry run option would do?
>
> If you just want to see if the file contains obvious corruption,
> you could do
>
> pg_restore file >/dev/null
>
> and see if it prints any complaints on stderr.  If you want to have
> confidence that the file would actually restore (and that there aren't
> e.g. unique-index violations or foreign-key violations in the data),
> I could imagine a mode where pg_restore wraps its output in "begin" and
> "rollback".  But that's not going to save any disk space, or time,
> compared to doing a normal restore into a scratch database.
>
> I can't think of any intermediate levels of verification that wouldn't
> involve a huge amount of work to implement ... and they'd be unlikely
> to catch interesting problems in practice.  For instance, I doubt that
> syntax-checking but not executing the SQL coming out of pg_restore would
> be worth the trouble.  If an archive is corrupt enough that it contains
> bad SQL, it probably has problems that pg_restore would notice anyway.
> Most of the restore failures that we hear about in practice would not be
> detectable without actually executing the commands, because they involve
> problems like issuing commands in the wrong order.
>
> regards, tom lane
>



--

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

[Steve Atkins]

> 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 15G  and 
> sometimes  i  have  no space  to restore it, so always i must declutter my  
> disk first. 
> 
> By the way i have programmed  backups on many databases so,  i must check the 
> integrity one by one  deleting the database  to avoid  disk space issues. By 
> the way the restores takes too long time an average of 1 hour by  backup.
> 
> 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.

If the gunzip completes successfully then the backups weren't corrupted and the 
disk is readable. They're very likely to be "good" unless you have a systematic 
problem with your backup script.

You could then run that data through pg_restore, redirecting the output to 
/dev/null, to check that the compressed file actually came from pg_dump. 
(gunzip backup_yesterday.gz | pg_restore >/dev/null)

The only level of checking you could do beyond that would be to ensure that the 
database was internally self-consistent and so truly restorable - and to do 
that, you'll need to restore it into a real database.

You could do an intermediate check by restoring into a real database with 
--schema-only, I guess.

As an aside, pg_dump with custom format already compresses the dump with gzip, 
so the additional gzip step may be redundant. You can set pg_dump's compression 
level with -Z.

Cheers,
  Steve


> 
> if pg_restore have a dry  option i  will do:
> 
> (gunzip -c  mydata.gz | pg_restore -d mydata --dry  &&  echo "mydata0 is ok")&
> (gunzip -c  my_other_data.gz | pg_restore -d my_other_data --dry  &&  echo 
> "my_other_data is ok")&
> (gunzip -c  my_another_data.gz | pg_restore -d my_another_data --dry  &&  
> echo "my_another_data is ok")&
> wait
> 
> 
> and  the time to  verify only will take 1 hour  instead of  3 hours.
> 



-- 
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] Would you add a --dry-run to pg_restore?

[Tom Lane]

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 space  to restore it, so always i must declutter my
>  disk first.

> 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.

What do you imagine a dry run option would do?

If you just want to see if the file contains obvious corruption,
you could do

pg_restore file >/dev/null

and see if it prints any complaints on stderr.  If you want to have
confidence that the file would actually restore (and that there aren't
e.g. unique-index violations or foreign-key violations in the data),
I could imagine a mode where pg_restore wraps its output in "begin" and
"rollback".  But that's not going to save any disk space, or time,
compared to doing a normal restore into a scratch database.

I can't think of any intermediate levels of verification that wouldn't
involve a huge amount of work to implement ... and they'd be unlikely
to catch interesting problems in practice.  For instance, I doubt that
syntax-checking but not executing the SQL coming out of pg_restore would
be worth the trouble.  If an archive is corrupt enough that it contains
bad SQL, it probably has problems that pg_restore would notice anyway.
Most of the restore failures that we hear about in practice would not be
detectable without actually executing the commands, because they involve
problems like issuing commands in the wrong order.

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

Re: [GENERAL] Do not INSERT if UPDATE fails

[David G. Johnston]

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 DDL commands by using a Common Table Expression (CTE)
(i.e., WITH / SELECT)​.  You would need to make it so the UPDATE happens
first and if there are no results the INSERT simply becomes a no-op.

David J.

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

[David G. Johnston]

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 accomplished via the "-l" (ell)
switch.  It means - "tell me what is going to happen but don't actually do
it".  I don't know what to call what you are describing but I don't see how
it could reasonably be made to work and give the admin confidence that a
true restoration would be valid.  Maybe I'm just being unimaginative but at
minimum you'd have to write out the tables to disk so data could be loaded
into them.  Then data would have to be persisted in order to validate the
constraints at the end.

If you are running out of disk space you should get larger/more disks.

Personally, I'd probably setup a dedicated "test restore" cluster with lots
of HD and put stuff like "fsync=off" into its postgresql.conf.

I could see having a "--make-tables-unlogged" option that would convert,
on-the-fly, all CREATE TABLE commands to "CREATE UNLOGGED TABLE" commands.

David J.

Re: [GENERAL] Do not INSERT if UPDATE fails

[Scott Marlowe]

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

[Scott Marlowe]

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 diskspace.
>
> I want to get the date from A into X and X will replicate into Y.
>
>
> I am currently on X using this command
>
> pg_dump -U  -h  -t BIGTABLE -a  | sudo -u postgres -i
> psql -q ;
>
> This is taking a long time, its been 2 days and I have xfered around 2T..
> This is just a test to see how long and to populate my new UAT env. so I
> will have to do it again.
>
> Problem is time.  the pg_dump process is single threaded.
> I have 2 routers in between A and X but its 10G networking - but my network
> graphs don't show much traffic.
>
> Server X is still in use, there are still records being inserted into the
> tables.
>
> How can I make this faster.
>
> I could shutdown server A and present the disks to server X, could I load
> this up in PGSQL and do a table to table copy - i presume this would be
> faster ... is this possible ?  how do I get around the same DB name ?
> What other solutions do I have ?

Yes, but if it's taking days to transfer 2TB then you need to
investigate where your performance is tanking.

Have you tried resyncing / scping files across the network to see how
fast your network connection is?

Have you tried just pg_dumping / restoring locally to get an idea how
fast you can dump / restore withoout doing it over a network
connection?

Are you IO bound? Network bound? CPU bound?

Is the destination copying data, or building indexes? Do you insert
into a schema that already has indexes in place? If so have you tried
dropping the indexes first and rebuilding them?


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

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

[Edmundo Robles]

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 first.

By the way i have programmed  backups on many databases so,  i must check
the integrity one by one  deleting the database  to avoid  disk space
issues. By the way the restores takes too long time an average of 1 hour by
 backup.

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.

if pg_restore have a dry  option i  will do:

(gunzip -c  mydata.gz | pg_restore -d mydata --dry  &&  echo "mydata0 is
ok")&
(gunzip -c  my_other_data.gz | pg_restore -d my_other_data --dry  &&  echo
"my_other_data is ok")&
(gunzip -c  my_another_data.gz | pg_restore -d my_another_data --dry  &&
 echo "my_another_data is ok")&
wait


and  the time to  verify only will take 1 hour  instead of  3 hours.

[GENERAL] Do not INSERT if UPDATE fails

[Alexander Farber]

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 text,
in_price float,
in_ip inet)
RETURNS integer AS
$func$
INSERT INTO words_payments (
sid,
social,
tid,
paid,
price,
ip
) VALUES (
in_sid,
in_social,
in_tid,
CURRENT_TIMESTAMP,
in_price,
in_ip
);

UPDATEwords_users u
SET   vip_until = CURRENT_TIMESTAMP + interval '1 year'
FROM  words_social s
WHERE s.sid = in_sid
AND   s.social = in_social
AND   u.uid = s.uid
AND   (u.vip_until IS NULL OR u.vip_until < CURRENT_TIMESTAMP)
RETURNING u.uid;

$func$ LANGUAGE sql;

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.

Is there please a way to rewrite the above function, without switching from
SQL to PL/pgSQL?

Regards
Alex

Re: [GENERAL] bidirectional mapping?

[Chris Travers]

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 want "A" and "B"
> to be a monogamous relationship because that is not always be true world
> wide. The best I can come up with so far is something like:
>
> CREATE TABLE forespouse (PERSON integer PRIMARY KEY,
> SPOUSE integer UNIQUE
> CHECK( PERSON != SPOUSE) -- sorry, can't marry self
> );
> CREATE UNIQUE INDEX ON forespouse(PERSON, SPOUSE);
> CREATE UNIQUE INDEX ON forespouse(SPOUSE, PERSON);
> -- I'm not sure that the above indices are needed.
>
> CREATE VIEW backspouse AS SELECT SPOUSE, PERSON FROM forespouse;
> CREATE VIEW spouse AS
>  SELECT PERSON, SPOUSE FROM forespouse
>  UNION
>  SELECT SPOUSE, PERSON FROM backspouse
> ;
>

Usually the way I have done this is to normalise the representation and use
a table method for converting for joins.  In other words:

create table marriage (party integer primary key, counterparty integer
unique, check party < counterparty);

This way you can ensure that each relationship is only recorded once.

Then I would create a function that returns an array of the parties.

CREATE OR REPLACE FUNCTION parties(marriage) returns int[] language sql as
$$
select array[$1.party, $1.counterparty];
$$;

Then you can create a gin index:

create index marriage_parties_idx on marriage using gin(parties(marriage));

Then you can query on:
select ... from people p1 where first_name = 'Ashley'
 join marriage m on p1 = any(marriage.parties)
 join people p2 on p2 = any(marriage.parties) and p2.id <> p1.id

>
>
> --
> Veni, Vidi, VISA: I came, I saw, I did a little shopping.
>
> Maranatha! <><
> John McKown
>



-- 
Best Wishes,
Chris Travers

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

[GENERAL] bidirectional mapping?

[John McKown]

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 wide.
The best I can come up with so far is something like:

CREATE TABLE forespouse (PERSON integer PRIMARY KEY,
SPOUSE integer UNIQUE
CHECK( PERSON != SPOUSE) -- sorry, can't marry self
);
CREATE UNIQUE INDEX ON forespouse(PERSON, SPOUSE);
CREATE UNIQUE INDEX ON forespouse(SPOUSE, PERSON);
-- I'm not sure that the above indices are needed.

CREATE VIEW backspouse AS SELECT SPOUSE, PERSON FROM forespouse;
CREATE VIEW spouse AS
 SELECT PERSON, SPOUSE FROM forespouse
 UNION
 SELECT SPOUSE, PERSON FROM backspouse
;


-- 
Veni, Vidi, VISA: I came, I saw, I did a little shopping.

Maranatha! <><
John McKown

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

[Melvin Davidson]

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: terminating connection due to
> administrator command
>
>
> Re-run the same cronjob several times, and cannot re-generate the error.
>
> May I know what might cause the above error message please? And which log
> file(online doc) could help provide more detail information about this
> please?
>
> Thanks a lot.
> --
> psql version: PostgreSQL 8.3.18 on x86_64-unknown-linux-gnu
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


This says it all:  "terminating connection due to administrator command"
It means someone killed your session, either from the command line with
kill {the pid}  or with SELECT pg_terminate_backend(pid);

*https://www.postgresql.org/docs/9.4/static/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL
*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

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

[Albe Laurenz]

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 cronjob several times, and cannot re-generate the error.
> 
> May I know what might cause the above error message please? And which
> log file(online doc) could help provide more detail information about
> this please?

You could look into the PostgreSQL server log.

An administrator killed your session, or statement_timeout was exceeded.

Yours,
Laurenz Albe

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

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

[Emi]

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 times, and cannot re-generate the error.

May I know what might cause the above error message please? And which 
log file(online doc) could help provide more detail information about 
this please?


Thanks a lot.
--
psql version: PostgreSQL 8.3.18 on x86_64-unknown-linux-gnu




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