Re: [GENERAL] How safe is pg_basebackup + continuous archiving?

[Michael Paquier]

On Wed, Jun 29, 2016 at 11:51 PM, Kaixi Luo  wrote:
> We use PostgreSQL at work and we do daily backups with pg_dump. After that
> we pg_restore the dump and check the database that there isn't any data
> corruption. As the database grows, the whole pg_dump / pg_restore cycle time
> is quickly approaching 24h, so we need to change strategies.

That's a mature solution. And without doubts, many systems in
production use it, abuse of it and rely on it.

> We've thought about using pg_basebackup + continuous archiving as an
> alternative backup process, but I have doubts regarding the safety of such
> procedure. As far as I know, pg_basebackup is done via rsync (and we also
> archive wals using rsync), so if by any chance  disk corruption occurs on
> the master server, the corruption would be carried over to our backup
> server.

pg_basebackup speaks the replication protocol and uses it to receive a
base backup from the server in the shape of a tar stream. It then
decides if it needs to untar the content or write it to disk as-is.
Note though that the contents of a backup are not fsync'd to disk
after pg_basebackup finished (that's in the works), so you had better
do it as well to ensure that the data stays here.

> How can we check for backup corruption in this case? Thanks you very much.

Before replaying a backup on a production system, you would need a
pre-production setup where the backup is replayed and checked.
Honestly, you can only be sure that a backup is working correctly
after reusing it. You could always do some validation of the raw
backup contents, but you need at the end the WAL applied on top of it
to be able to check the status of a server that has reached a
consistent point.
-- 
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] Stored procedure version control

[Neil Anderson]

On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote:

Elsewhere, somebody was asking how people implemented version control
for stored procedures on (MS) SQL Server.

The consensus was that this is probably best managed by using scripts or
command files to generate stored procedures etc., but does anybody have
any comment on that from the POV of PostgreSQL?



I can't comment from the POV of those who represent Postgres, but I used 
to work for a company who specialised in change management for database 
products, SQL Server and Oracle in particular. There are at least two 
approaches. The migrations approach and the state based approach.


For migrations you create up and down scripts/code fragments to move the 
database through versions over time, committing them to a source control 
system as you go. Usually the database will contain some tables to keep 
track of the current live version.


With the state based approach you just store the DDL for each object in 
the source control system. You can see how an object changes over time 
by just inspecting one file. You can automate the scripting process or 
use one of the diffing tools that supports comparing to DDL directly.


State based handles merge conflicts better than migrations. Migrations 
handles data changes better than state based. Migrations also is better 
if you are deploying to multiple production databases that may all be on 
different versions.


If your database contains a lot of logic or you have a large distributed 
team you are more likely to have merge issues and so state based is 
probably the better choice. Smaller team, less logic and a production 
environment where you need to be able to update from any version 
reliably? Migrations is a good choice.


Additionally you don't have to stick with one or the other. In the early 
days while you have little data to worry about you might use the static 
approach and then switch to migrations. You just pick a baseline to 
start from and carry on from there.


--
Neil Anderson
n...@postgrescompare.com
http://blog.postgrescompare.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] Stored procedure version control

[Neil Anderson]

On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote:

Elsewhere, somebody was asking how people implemented version control
for stored procedures on (MS) SQL Server.

The consensus was that this is probably best managed by using scripts or
command files to generate stored procedures etc., but does anybody have
any comment on that from the POV of PostgreSQL?



I can't comment from the POV of those who represent Postgres, but I used 
to work for a company who specialised in change management for database 
products, SQL Server and Oracle in particular. There are at least two 
approaches. The migrations approach and the state based approach.


For migrations you create up and down scripts/code fragments to move the 
database through versions over time, committing them to a source control 
system as you go. Usually the database will contain some tables to keep 
track of the current live version.


With the state based approach you just store the DDL for each object in 
the source control system. You can see how an object changes over time 
by just inspecting one file. You can automate the scripting process or 
use one of the diffing tools that supports comparing to DDL directly.


State based handles merge conflicts better than migrations. Migrations 
handles data changes better than state based. Migrations also is better 
if you are deploying to multiple production databases that may all be on 
different versions.


If your database contains a lot of logic or you have a large distributed 
team you are more likely to have merge issues and so state based is 
probably the better choice. Smaller team, less logic and a production 
environment where you need to be able to update from any version 
reliably? Migrations is a good choice.


Additionally you don't have to stick with one or the other. In the early 
days while you have little data to worry about you might use the static 
approach and then switch to migrations. You just pick a baseline to 
start from and carry on from there.


--
Neil Anderson
n...@postgrescompare.com
http://blog.postgrescompare.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] Stored procedure version control

[Peter Devoy]

>does anybody have any comment on that from the POV of PostgreSQL?

Might be overkill but you could deploy your procedure as an extension
because extensions come with version control:
https://www.postgresql.org/docs/current/static/sql-createextension.html

Another option might be to hack something together using COMMENT ON
FUNCTION [...]:
https://www.postgresql.org/docs/current/static/sql-comment.html

Kind regards


Peter


-- 
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] Stored procedure version control

[Scott Marlowe]

On Wed, Jun 29, 2016 at 12:00 PM, Adrian Klaver
 wrote:
> On 06/29/2016 09:37 AM, Mark Morgan Lloyd wrote:
>>
>> Elsewhere, somebody was asking how people implemented version control
>> for stored procedures on (MS) SQL Server.
>>
>> The consensus was that this is probably best managed by using scripts or
>> command files to generate stored procedures etc., but does anybody have
>> any comment on that from the POV of PostgreSQL?
>
>
> There is no mechanism internal to Postgres that will version control the
> procedures, so the answer will be the same as above. In other words some
> external mechanism to version control. A more complete answer will depend on
> the workflow you are currently using.

I like this: https://github.com/depesz/Versioning very simple and easy to use.


-- 
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] Stored procedure version control

[Adrian Klaver]

On 06/29/2016 09:37 AM, Mark Morgan Lloyd wrote:

Elsewhere, somebody was asking how people implemented version control
for stored procedures on (MS) SQL Server.

The consensus was that this is probably best managed by using scripts or
command files to generate stored procedures etc., but does anybody have
any comment on that from the POV of PostgreSQL?


There is no mechanism internal to Postgres that will version control the 
procedures, so the answer will be the same as above. In other words some 
external mechanism to version control. A more complete answer will 
depend on the workflow you are currently using.




--
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] Stored procedure version control

[Mark Morgan Lloyd]

Elsewhere, somebody was asking how people implemented version control 
for stored procedures on (MS) SQL Server.


The consensus was that this is probably best managed by using scripts or 
command files to generate stored procedures etc., but does anybody have 
any comment on that from the POV of PostgreSQL?


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]


--
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] cache lookup failed for index

[Willy-Bas Loos]

On Wed, Jun 29, 2016 at 4:26 PM, Tom Lane  wrote:

> Willy-Bas Loos  writes:
> > So what i don't get is, -if the above is the case- If  pg_dump expects to
> > find an index, it already knows about its existence. Then why does it
> need
> > to look for it again?
>
>
> pg_dump can't tell the index is no longer there --- but some of the
> backend functions it calls can tell, and they throw errors.
>
> There are various ways this might be rejiggered, but none of them
> entirely remove all risk of failure in the presence of concurrent DDL.
> Personally I'd recommend just retrying the pg_dump until it succeeds.
>
> regards, tom lane
>

Now that i know what it is, I can live with it.
Thanks for the insight!

-- 
Willy-Bas Loos

[GENERAL] How safe is pg_basebackup + continuous archiving?

[Kaixi Luo]

Hello,

We use PostgreSQL at work and we do daily backups with pg_dump. After that
we pg_restore the dump and check the database that there isn't any data
corruption. As the database grows, the whole pg_dump / pg_restore cycle
time is quickly approaching 24h, so we need to change strategies.

We've thought about using pg_basebackup + continuous archiving as an
alternative backup process, but I have doubts regarding the safety of such
procedure. As far as I know, pg_basebackup is done via rsync (and we also
archive wals using rsync), so if by any chance  disk corruption occurs on
the master server, the corruption would be carried over to our backup
server.

How can we check for backup corruption in this case? Thanks you very much.

Kaixi Luo

Re: [GENERAL] cache lookup failed for index

[Tom Lane]

Willy-Bas Loos  writes:
> So what i don't get is, -if the above is the case- If  pg_dump expects to
> find an index, it already knows about its existence. Then why does it need
> to look for it again?

Because what it does is:

BEGIN ISOLATION LEVEL REPEATABLE READ;  -- run in a single transaction
SELECT ... FROM pg_class;  -- find out what all the tables are
LOCK TABLE foo IN ACCESS SHARE MODE;  -- repeat for each table to be dumped

after which it runs around and collects subsidiary data such as what
indexes exist for each table.  But the transaction's view of the catalogs
was frozen at the start of the first SELECT.  So it can see entries for
an index in pg_class and pg_index even if that index got dropped between
transaction start and where pg_dump was able to lock the index's table.
pg_dump can't tell the index is no longer there --- but some of the
backend functions it calls can tell, and they throw errors.

There are various ways this might be rejiggered, but none of them
entirely remove all risk of failure in the presence of concurrent DDL.
Personally I'd recommend just retrying the pg_dump until it succeeds.

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] Enquiry

[Melvin Davidson]

Almost all databases are "scalable". The scalability is based on schema
design and hardware architecture. As long as you are careful about table
structures and relationships and have sufficient hardware, it should scale
nicely.

What does "scalable database" mean?



On Wed, Jun 29, 2016 at 9:55 AM, Andreas Joseph Krogh 
wrote:

> På onsdag 29. juni 2016 kl. 09:36:12, skrev mohd abdul azeem <
> mohdabdulaz...@hotmail.com>:
>
> Hello
>
> This is Mohammed. I am a database developer using postgres as the backend
> database. i am working on the postgres version 9.3.5 for development
> purpose.
>
> I have just signed up with the postgres community. i hope this is correct
> place for getting my queries answered.
>
> I am planning to learn the scalability feature in postgres. found that
> there is no inbuilt support in postgres.
>
> it would be great if you could point me in the right direction.
>
> Thanks
> Mohammed Abdul Azeem
>
>
> It would make it easier for us to help you if you asked a specific
> question.
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andr...@visena.com
> www.visena.com
> 
>
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Enquiry

[Adrian Klaver]

On 06/29/2016 12:36 AM, mohd abdul azeem wrote:

Hello

This is Mohammed. I am a database developer using postgres as the
backend database. i am working on the postgres version 9.3.5 for
development purpose.


Any particular reason you are using this version instead of the most 
recent, 9.5.3  or is this a reversed number issue?





I have just signed up with the postgres community. i hope this is
correct place for getting my queries answered.


Yes.



I am planning to learn the scalability feature in postgres. found that
there is no inbuilt support in postgres.

it would be great if you could point me in the right direction.


See the post from Andreas.



Thanks
Mohammed Abdul Azeem



--
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] Enquiry

[Andreas Joseph Krogh]

På onsdag 29. juni 2016 kl. 09:36:12, skrev mohd abdul azeem <
mohdabdulaz...@hotmail.com >:
Hello  
This is Mohammed. I am a database developer using postgres as the backend 
database. i am working on the postgres version 9.3.5 for development purpose.
 
I have just signed up with the postgres community. i hope this is correct 
place for getting my queries answered.  
 
I am planning to learn the scalability feature in postgres. found that there 
is no inbuilt support in postgres. 
 
it would be great if you could point me in the right direction.
 
Thanks
Mohammed Abdul Azeem

 
It would make it easier for us to help you if you asked a specific question.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

[GENERAL] Enquiry

[mohd abdul azeem]

Hello
This is Mohammed. I am a database developer using postgres as the backend 
database. i am working on the postgres version 9.3.5 for development purpose.
I have just signed up with the postgres community. i hope this is correct place 
for getting my queries answered.  
I am planning to learn the scalability feature in postgres. found that there is 
no inbuilt support in postgres. 
it would be great if you could point me in the right direction.
ThanksMohammed Abdul Azeem

Re: [GENERAL] Sub-query having NULL row returning FALSE result

[David G. Johnston]

On Wed, Jun 29, 2016 at 2:07 AM, Sridhar N Bamandlapally <
sridhar@gmail.com> wrote:

> Hi
>
> Please go through below case
>
> postgres=# CREATE TABLE emp (id INTEGER unique, ename VARCHAR);
> CREATE TABLE
> postgres=# INSERT INTO emp VALUES (null, 'aaa');
> INSERT 0 1
> postgres=# INSERT INTO emp VALUES (null, 'bbb');
> INSERT 0 1
> postgres=# INSERT INTO emp VALUES (3, 'ccc');
> INSERT 0 1
> postgres=# INSERT INTO emp VALUES (4, 'ddd');
> INSERT 0 1
> postgres=# SELECT * FROM emp ;
>  id | ename
> +---
> | aaa
> | bbb
>   3 | ccc
>   4 | ddd
> (4 rows)
>
> postgres=# SELECT * FROM (SELECT 5::integer id, 'eee'::varchar ename) nr;
>  id | ename
> +---
>   5 | eee
> (1 row)
>
> postgres=# INSERT INTO emp SELECT * FROM (SELECT 5::integer id,
> 'eee'::varchar ename) nr WHERE id NOT IN (SELECT id FROM emp);
> INSERT 0 0
> postgres=# SELECT * FROM emp ;
>  id | ename
> +---
> | aaa
> | bbb
>   3 | ccc
>   4 | ddd
> (4 rows)
>
> postgres=#
>
>
> The application is generating SQL-Statement to avoid exception while
> inserting
>
> The expected behavior is to INSERT row if the NEW id is not existing in
> table, but this is doing FALSE
>
> Please advise me if am doing something wrong here or any alternate
>

​Subjectively, you are allowing an ID field to be NULL.  That, for me, is
wrong.​

​Given this, as a follow-up to what Tom said, you need to decide what you
wish to happen for your NULL IDs.  Until you explain that behavior it is
not possible to provide valid alternatives.​

​Usually you want to use "EXISTS", not "IN"​

​
https://www.postgresql.org/docs/9.6/static/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS

Oh, and try adding "WHERE id IS NOT NULL"​

​David J.​

Re: [GENERAL] cache lookup failed for index

[Willy-Bas Loos]

On Wed, Jun 29, 2016 at 11:16 AM, Willy-Bas Loos  wrote:

> Ah OK. So it needs a lock for the visibility to be registered to the
> transaction, is that it?
>

Wait that doesn't make sense. pg_dump merely describes the table in DDL, so
it doesn't even need access to the index pages. It only needs to read the
pg_catalog afaik.
However, the indexes are created as a last step in the pg_dump process (at
least in the resulting dump file). So it first makes the DDL to create the
table, then dumps the data, and in the end it describes the constraints,
indexes and privileges.
So maybe pg_dump first creates the DDL, then acquires a access share lock
on the table for dumping the data, and then when it's time to create DDL
for the index, it is gone from the pg_catalog.
So what i don't get is, -if the above is the case- If  pg_dump expects to
find an index, it already knows about its existence. Then why does it need
to look for it again?

-- 
Willy-Bas Loos

Re: [GENERAL] cache lookup failed for index

[Willy-Bas Loos]

Ah OK. So it needs a lock for the visibility to be registered to the
transaction, is that it?

Re: [GENERAL] Sub-query having NULL row returning FALSE result

[Sridhar N Bamandlapally]

Hi

The actual statement is MERGE INTO  NOT MATCHED, which in PG
migrated to WITH - INSERT

however, yes, the SQL-statement in previous does not work in other
databases too, I was wrong

Thanks, thanks again
Sridhar
OpenText


On Wed, Jun 29, 2016 at 11:58 AM, Tom Lane  wrote:

> Sridhar N Bamandlapally  writes:
> > postgres=# CREATE TABLE emp (id INTEGER unique, ename VARCHAR);
> > postgres=# INSERT INTO emp VALUES (null, 'aaa');
> > ...
> > postgres=# INSERT INTO emp SELECT * FROM (SELECT 5::integer id,
> > 'eee'::varchar ename) nr WHERE id NOT IN (SELECT id FROM emp);
> > INSERT 0 0
>
> This is expected.  NOT IN can never succeed if there are any nulls
> returned by the sub-select, because the nulls represent "unknown",
> and so it's unknown whether there is a match to the outer "id"
> value, and WHERE takes a null (unknown) result as false not true.
> Certainly there are things to quibble with in that behavior, but
> it's what's been required by the SQL standard since 1992.
>
> > but this is working with other databases
>
> Really?  None that are compliant with the SQL standard, for sure.
>
> regards, tom lane
>