Re: [GENERAL] PGPASSWORD - More than one in a bash script

[Adrian Klaver]

On 08/17/2016 09:01 PM, Patrick B wrote:

Hi guys,

I'm writing a bash script to dump and restore (pg_dump + pg_restore) a
test database.

However, the username to access the pg_dump server is different of the
one to access pg_restore.

I'm using the PGPASSWORD parameter on the script, but can I put two of that?

like:

PGPASSWORD
PGPASSWORD2

If not, do you guys have any idea how I could do this?


https://www.postgresql.org/docs/9.5/static/libpq-pgpass.html



I'm using PostgreSQL 9.5

cheers




--
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] PGPASSWORD - More than one in a bash script

[Patrick B]

Hi guys,

I'm writing a bash script to dump and restore (pg_dump + pg_restore) a test
database.

However, the username to access the pg_dump server is different of the one
to access pg_restore.

I'm using the PGPASSWORD parameter on the script, but can I put two of that?

like:

> PGPASSWORD
> PGPASSWORD2

If not, do you guys have any idea how I could do this?

I'm using PostgreSQL 9.5

cheers

Re: [GENERAL] Critical failure of standby

[James Sewell]

Hi,

No, this was a one off in a network split situation.

I'll check the startup when I get a chance - thanks for the help.

Cheers,

James Sewell,
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 Tue, Aug 16, 2016 at 5:55 PM, Simon Riggs  wrote:

> On 16 August 2016 at 08:11, James Sewell 
> wrote:
>
>
>> As per the logs there was a crash of one standby, which seems to have
>> corrupted that standby and the two cascading standby.
>>
>>- No backups
>>- Full page writes enabled
>>- Fsync enabled
>>
>> WAL records are CRC checked, so it may just be a bug, not corruption that
> affects multiple servers.
>
> At the moment we know the Startup process died, but we don't know why.
>
> Do you repeatedly get this error?
>
> Please set log_error_verbosity = VERBOSE and rerun
>
> --
> Simon Riggshttp://www.2ndQuadrant.com/
> 
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

-- 

--
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] Permissions pg_dump / import

[Patrick B]

2016-08-18 10:30 GMT+12:00 Ilya Kazakevich :

> >> Owned by: public.accounts.id
>
> This is  not owner but table this sequence depends on. See
> http://stackoverflow.com/questions/6941043/get-table-
> and-column-owning-a-sequence
>
>
>
> Use query provided on SO to get real owner
>
>
>
>
> Thanks guys... the "--no-owner" option helped a lot.

Cheers
Patrick

Re: [GENERAL] Re: Easiest way to compare the results of two queries row by row and column by column

[Branden Visser]

On Wed, Aug 17, 2016 at 4:58 AM, gilad905  wrote:
> Vik, note that your new suggestion for a query might be more
> readable/efficient than Jeff's, but IT STILL DOESN'T SOLVE THE PROBLEM OF
> DETECTING DUPLICATE ROWS.
>

I've done this in the past and in my use-case it was easy enough to
export the rows to CSV with a sort, and then use a diff tool to ensure
they're identical. My data had 100's of thousands of rows, but
obviously with massive tables this may not be appropriate.

If you want more details on this I can find the exact psql client
query form I used to get a CSV file of the results.

Hope that helps.

Branden
>
>
> --
> View this message in context: 
> http://postgresql.nabble.com/Easiest-way-to-compare-the-results-of-two-queries-row-by-row-and-column-by-column-tp5760209p5916761.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


-- 
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] Permissions pg_dump / import

[Ilya Kazakevich]

>> Owned by: public.accounts.id 

This is  not owner but table this sequence depends on. See 
http://stackoverflow.com/questions/6941043/get-table-and-column-owning-a-sequence

 

Use query provided on SO to get real owner

 

Ilya Kazakevich

 

JetBrains

http://www.jetbrains.com  

The Drive to Develop

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Patrick B
Sent: Thursday, August 18, 2016 1:17 AM
To: pgsql-general
Subject: [GENERAL] Permissions pg_dump / import

 

Hi guys,

 

I'm running a pg_dump and then importing the dump into a test server. I'm using 
PostgreSQL 9.5.

 

pg_dump:

pg_dump --dbname=prod1 --host= servername --port=5432 --username=user1 -v -f 
test1_NEW.sql

Steps into the new database (test1):

CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO user1;
psql -h servername -U master --port=5432 --dbname=test1 -f test1_NEW.sql


I get lots of errors like:

psql:test1_NEW.sql:212: ERROR:  must be owner of relation accounts_id_seq

 

prod1=> \d+ accounts_id_seq
Sequence "public.accounts_id_seq"
Column |  Type   |Value| Storage 
---+-+-+-
 sequence_name | name| accounts_id_seq | plain
 last_value| bigint  | 33  | plain
 start_value   | bigint  | 1   | plain
 increment_by  | bigint  | 1   | plain
 max_value | bigint  | 9223372036854775807 | plain
 min_value | bigint  | 1   | plain
 cache_value   | bigint  | 1   | plain
 log_cnt   | bigint  | 32  | plain
 is_cycled | boolean | f   | plain
 is_called | boolean | t   | plain
Owned by: public.accounts.id 

 

What do I have to do? Should I revoke the permissions on the prod1 database 
before performing the dump?

 

Cheers;

Patrick 

Re: [GENERAL] Permissions pg_dump / import

[Steve Crawford]

Check out the --no-owner and/or --no-acl flags when performing the dump.
These eliminate the statements that set and/or alter ownership of database
objects.

For use in a test server where the username of the test-server database is
different than the username on the production server *and* where you don't
have lots of roles with different ownership and permissions across your
database you should be fine.

Or create role(s) on your test database that match those on the production
database. This may require updating pg_hba.conf on the test database.

Cheers,
Steve


On Wed, Aug 17, 2016 at 3:16 PM, Patrick B  wrote:

> Hi guys,
>
> I'm running a pg_dump and then importing the dump into a test server. I'm
> using PostgreSQL 9.5.
>
> *pg_dump:*
>
>> pg_dump --dbname=prod1 --host= servername --port=5432 --username=user1 -v
>> -f test1_NEW.sql
>
> *Steps into the new database (test1):*
>
>> CREATE SCHEMA public;
>> GRANT ALL ON SCHEMA public TO user1;
>> psql -h servername -U master --port=5432 --dbname=test1 -f test1_NEW.sql
>
>
> I get lots of errors like:
>
> psql:test1_NEW.sql:212: ERROR:  must be owner of relation accounts_id_seq
>
>
> prod1=> \d+ accounts_id_seq
>> Sequence "public.accounts_id_seq"
>> Column |  Type   |Value| Storage
>> ---+-+-+-
>>  sequence_name | name| accounts_id_seq | plain
>>  last_value| bigint  | 33  | plain
>>  start_value   | bigint  | 1   | plain
>>  increment_by  | bigint  | 1   | plain
>>  max_value | bigint  | 9223372036854775807 | plain
>>  min_value | bigint  | 1   | plain
>>  cache_value   | bigint  | 1   | plain
>>  log_cnt   | bigint  | 32  | plain
>>  is_cycled | boolean | f   | plain
>>  is_called | boolean | t   | plain
>> Owned by: public.accounts.id
>
>
> What do I have to do? Should I revoke the permissions on the prod1
> database before performing the dump?
>
> Cheers;
> Patrick
>

[GENERAL] Permissions pg_dump / import

[Patrick B]

Hi guys,

I'm running a pg_dump and then importing the dump into a test server. I'm
using PostgreSQL 9.5.

*pg_dump:*

> pg_dump --dbname=prod1 --host= servername --port=5432 --username=user1 -v
> -f test1_NEW.sql

*Steps into the new database (test1):*

> CREATE SCHEMA public;
> GRANT ALL ON SCHEMA public TO user1;
> psql -h servername -U master --port=5432 --dbname=test1 -f test1_NEW.sql


I get lots of errors like:

psql:test1_NEW.sql:212: ERROR:  must be owner of relation accounts_id_seq


prod1=> \d+ accounts_id_seq
> Sequence "public.accounts_id_seq"
> Column |  Type   |Value| Storage
> ---+-+-+-
>  sequence_name | name| accounts_id_seq | plain
>  last_value| bigint  | 33  | plain
>  start_value   | bigint  | 1   | plain
>  increment_by  | bigint  | 1   | plain
>  max_value | bigint  | 9223372036854775807 | plain
>  min_value | bigint  | 1   | plain
>  cache_value   | bigint  | 1   | plain
>  log_cnt   | bigint  | 32  | plain
>  is_cycled | boolean | f   | plain
>  is_called | boolean | t   | plain
> Owned by: public.accounts.id


What do I have to do? Should I revoke the permissions on the prod1 database
before performing the dump?

Cheers;
Patrick

[GENERAL] Re: Easiest way to compare the results of two queries row by row and column by column

[gilad905]

Vik, note that your new suggestion for a query might be more
readable/efficient than Jeff's, but IT STILL DOESN'T SOLVE THE PROBLEM OF
DETECTING DUPLICATE ROWS.



--
View this message in context: 
http://postgresql.nabble.com/Easiest-way-to-compare-the-results-of-two-queries-row-by-row-and-column-by-column-tp5760209p5916761.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] pgbasebackup is failing after truncate

[Yelai, Ramkumar]

Hi,

I have only one database and I ran query with superuser privilege.

Seems this issue is related to BUG 14243.

Regards,
Ramkumar.
-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Friday, August 12, 2016 7:37 PM
To: Yelai, Ramkumar (CT DD DS AA DF-PD FH ES); pgsql-general@postgresql.org
Subject: Re: [GENERAL] pgbasebackup is failing after truncate

On 08/10/2016 11:06 PM, Yelai, Ramkumar wrote:
> HI
>
> At present, I have some requirement to truncate the data base  to 
> reclaim disk space and at the same time I need to take basebackup.

To be clear you ran TRUNCATE on tables within the database, correct?

If not what was the actual command you used?

>
> Seems, truncate is successfully reclaimed the space but pgbasebackup 
> failed and reported the below error.
>
> "could not stat file or directory ./base/16384/25600: Permission denied.

So what user did you run pg_basebackup  as and does that user have rights on 
that file/directory?

>
> Further observation shows that truncate command reclaiming the space 
> by making empty (not deleted)  these files in ./base/16384 folders.
>
> Seems these filename are relfilenode in pg_class table to map filename 
> to table name. Since truncate making empty these files and create new 
> files to map the old table name, pgbasebackup is unable to synch with 
> truncate and pointing to old files.
>
> Also, the old files are not deleted from "base" folder.
>
> Please let me know how to resolve this issue
>
> With best regards,
> Ramkumar Yelai
>
> _mailto:ramkumar.yelai@siemens.com_
> _http://www.siemens.co.in/STS_
>
> Registered Office: 130, Pandurang Budhkar Marg, Worli, Mumbai 400 018.
> Telephone +91 22 39677000. Fax +91 22 39677075. Other Offices:
> Bangalore, Chennai, Gurgaon, Noida, Kolkata, Pune. Corporate Identity
> number:U9MH1986PLC093854
>
>


--
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] pgbasebackup is failing after truncate

[Yelai, Ramkumar]

HI,

Thanks for helpful information.

Even if there is no way to fix then please let me know how long that file ( 
pending  for deletion ) will be present in that directory.

I observe like 1 or 2 hr after it will be deleted. But I am wondering, who is 
deleting that file. 

Regards,
Ramkumar


-Original Message-
From: Michael Paquier [mailto:michael.paqu...@gmail.com] 
Sent: Tuesday, August 16, 2016 5:51 AM
To: Jeff Janes
Cc: Yelai, Ramkumar (CT DD DS AA DF-PD FH ES); pgsql-general@postgresql.org
Subject: Re: [GENERAL] pgbasebackup is failing after truncate

On Sat, Aug 13, 2016 at 12:41 AM, Jeff Janes  wrote:
> On Wed, Aug 10, 2016 at 11:06 PM, Yelai, Ramkumar 
>  wrote:
>> At present, I have some requirement to truncate the data base  to 
>> reclaim disk space and at the same time I need to take basebackup.
>>
>> Seems, truncate is successfully reclaimed the space but pgbasebackup 
>> failed and reported the below error.
>>
>> “could not stat file or directory ./base/16384/25600: Permission denied.
>
> Are you on MS Windows?  If so, sounds like a possible variant of BUG 
> #14243

That's really the same issue... See here for more details:
https://www.postgresql.org/message-id/20160712083220.1426.58...@wrigleys.postgresql.org
If you have ideas about making a difference between a real permission error and 
STATUS_PENDING_DELETE, that would be great.
--
Michael

-- 
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] regexp_replace double quote

[Михаил]

Thank you!

2016-08-15 18:36 GMT+05:00, hubert depesz lubaczewski :
> On Mon, Aug 15, 2016 at 06:27:06PM +0500, Михаил wrote:
>> I need to escape double quotes only:
>> test=# select regexp_replace('"""{Performer,"Boomwacker ""a""
>> Recording""}"""', '([^"])"{2}([^"])', '\1\"\2', 'g');
>>  regexp_replace
>> -
>>  """{Performer,"Boomwacker \"a"" Recording\"}"""
>>
>> This is unexpected result.
>>
>> But when added one symbol to ""a"" the result is right:
>> test=# select regexp_replace('"""{Performer,"Boomwacker ""a1""
>> Recording""}"""', '([^"])"{2}([^"])', '\1\"\2', 'g');
>>   regexp_replace
>> --
>>  """{Performer,"Boomwacker \"a1\" Recording\"}"""
>
> This is because when finding first "", "a" that is afterwards get
> assigned to \2. and thus is already "used", and can't be part of
> match for the second "".
>
> What will solve the problem is to use lookahead, like:
> $ select regexp_replace('"""{Performer,"Boomwacker ""a"" Recording""}"""',
> '([^"])"{2}(?=[^"])', '\1\"', 'g');
>  regexp_replace
> -
>  """{Performer,"Boomwacker \"a\" Recording\"}"""
> (1 row)
>
> because then the part inside (?=...) is not "used", and can be used for
> next
> match.
>
> Not sure if I'm clear, but hopefully you'll understand what I'm trying to
> explain :)
>
> Best regards,
>
> depesz
>
>


-- 
---
Regards,

Mikhail


-- 
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] 9.2 to 9.5 pg_upgrade losing data

[Pete Fuller]

We first noticed the issue after running the generated analyze script, in 
subsequent tests it shows up before the analyze though. 

Ran thru the process as a test monday night and it worked.  This time however, 
I deleted the complete 9.2 data directory before running our rsync scripts that 
refresh the data and reestablish replication to the master before the attempt.  
Also disabled all crons and monitoring (zabbix) that could possibly query the 
server during the attempt.   
Will attempt this again tonight using the same process and see if it will work 
correctly again.  





> On Aug 15, 2016, at 4:36 PM, Melvin Davidson  wrote:
> 
> 
> 
> On Mon, Aug 15, 2016 at 3:56 PM, Bruce Momjian  > wrote:
> On Mon, Aug 15, 2016 at 12:18:04PM -0700, Adrian Klaver wrote:
> > https://www.postgresql.org/docs/9.5/static/pgupgrade.html 
> > 
> >
> > "Obviously, no one should be accessing the clusters during the upgrade.
> > pg_upgrade defaults to running servers on port 50432 to avoid unintended
> > client connections. You can use the same port number for both clusters when
> > doing an upgrade because the old and new clusters will not be running at the
> > same time. However, when checking an old running server, the old and new
> > port numbers must be different."
> >
> > In your OP you do not show overriding pg_upgrade defaults for ports, so
> > assuming the scripts are looking for the live ports and not the upgrade
> > ports that should not be an issue.
> 
> Agreed.  I have no idea what would cause this, and have never heard a
> report like this before.
> 
> --
>   Bruce Momjian  mailto:br...@momjian.us>>
> http://momjian.us 
>   EnterpriseDB http://enterprisedb.com 
> 
> 
> + As you are, so once was I. As I am, so you will be. +
> + Ancient Roman grave inscription +
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org 
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general 
> 
> 
> Just out of curiosity, have you you ANALYZE on you db after the upgrade but 
> before doing a count compare?
> 
> -- 
> Melvin Davidson
> I reserve the right to fantasize.  Whether or not you 
> wish to share my fantasy is entirely up to you.