Re: [HACKERS] Dumping database creation options and ACLs

[Rafael Martinez]

On 06/29/2017 06:30 PM, Adrien Nayrat wrote:

> As reported by Ronan there's no other option than using pg_dumpall to restore
> database options and ACLs.
> 
> So, we use this trick to stop pg_dumpall before \connect and then use 
> pg_restore:
> 
> pg_dumpall -s | sed -rn '/^\\connect/{q}; p' > database+grants.sql
> 
> 
> Of course, it is not graceful as we just need results of pg_dumpall -g and 
> what
> the dumpCreateDB() function outputs.
> 
> What do you think about adding an option like --createdb-only (as suggested by
> Ronan) for this?  I'm not fully satisfied with this name though, I'll be happy
> if you have a better suggestion.
> 

Hello

We have a discussion about this some time ago and we created a wiki page
where we tried to write down some ideas/proposals and links to threads
discussing the subject:

https://wiki.postgresql.org/wiki/Pg_dump_improvements

regards,
-- 
 Rafael Martinez Guerrero
 Center for Information Technology
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/


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

Re: [HACKERS] open and close columns in the NEW record not allowed

[Rafael Martinez Guerrero]

On Thu, 2014-02-06 at 07:11 -0800, Adrian Klaver wrote:
> On 02/06/2014 06:35 AM, Rafael Martinez Guerrero wrote:

> > We think the behavior should be consistent, either it is allow to use
> > them or not, but not like it is today.
> >
> 
> " As a general rule, if you get spurious parser errors for commands that 
> contain any of the listed key words as an identifier you should try to 
> quote the identifier to see if the problem goes away."
> 
> 
> Which indeed solves the problem on my end at least:
> 

Hello

Thanks for the feedback.

Our problem is that an application decides the name of the columns in
the tables and "XDB replication" from EnterpriseDB decides the triggers.
We have no control over the code :-( 

regards,
-- 
Rafael Martinez Guerrero
Center for Information Technology Services
University of Oslo, Norway




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

[HACKERS] open and close columns in the NEW record not allowed

[Rafael Martinez Guerrero]

Hello

One of our users is having a problem with a trigger in a system running
postgresql 9.3.

The problem is that pl/pgsql does not accept open and close as column
names when used in the NEW record in a trigger function.

This page:
http://www.postgresql.org/docs/9.3/static/sql-keywords-appendix.html
does not say that they are reserved words in postgresql (although they
are reserved words in the sql standard)

In the other hand, postgres allows to create and update tables with
columns named open/close without problems.

We think the behavior should be consistent, either it is allow to use
them or not, but not like it is today.

-
Test case:
-
CREATE TABLE test_open(id integer,open timestamp);
CREATE TABLE test_close(id integer,close timestamp);
CREATE TABLE test_close_trigger(id integer,close timestamp);
CREATE TABLE test_open_trigger(id integer,open timestamp);

CREATE OR REPLACE FUNCTION test_open()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
 INSERT INTO test_open_trigger (id, open)
 VALUES (NEW.id, NEW.open);
 RETURN NEW;
END;
$function$;

CREATE OR REPLACE FUNCTION test_close()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
 INSERT INTO test_close_trigger (id, close)
 VALUES (NEW.id, NEW.close);
 RETURN NEW;
END;
$function$;

# INSERT INTO test_open (id,open) VALUES (1,now());
INSERT 0 1
# INSERT INTO test_close (id,close) VALUES (1,now());
INSERT 0 1
# SELECT * FROM test_open;
 id |open
+
  1 | 2014-02-06 15:17:52.654977
(1 row)

# SELECT * FROM test_close;
 id |   close
+
  1 | 2014-02-06 15:17:53.893911
(1 row)

CREATE TRIGGER test_open AFTER INSERT ON test_open FOR EACH ROW EXECUTE
PROCEDURE test_open();

CREATE TRIGGER test_close AFTER INSERT ON test_close FOR EACH ROW
EXECUTE PROCEDURE test_close();

# INSERT INTO test_open (id,open) VALUES (1,now());
ERROR:  record "new" has no field "open"
LINE 3:  VALUES (NEW.id, NEW.open)
 ^
QUERY:  INSERT INTO public.test_open_trigger
 (id, open)
 VALUES (NEW.id, NEW.open)
CONTEXT:  PL/pgSQL function test_open() line 3 at SQL statement
# INSERT INTO test_close (id,close) VALUES (1,now());
ERROR:  record "new" has no field "close"
LINE 3:  VALUES (NEW.id, NEW.close)
 ^
QUERY:  INSERT INTO public.test_close_trigger
 (id, close)
 VALUES (NEW.id, NEW.close)
CONTEXT:  PL/pgSQL function test_close() line 3 at SQL statement

-

Thanks in advance.

regards,
-- 
Rafael Martinez Guerrero
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/


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

Re: [HACKERS] pg_dump and pg_dumpall in real life (proposal)

[Rafael Martinez Guerrero]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/12/2013 03:28 PM, Stephen Frost wrote:
> * Rafael Martinez (r.m.guerr...@usit.uio.no) wrote:
>> Comments?
> 
> Create a wiki page for it. :)
> 

What about this to start with?:
https://wiki.postgresql.org/wiki/Pg_dump_improvements

- -- 
Rafael Martinez Guerrero
Center for Information Technology
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iEYEARECAAYFAlKCTfAACgkQBhuKQurGihSrYQCeKyYVthpbk47hGjayBjidqaFL
nysAn3JJjGT/8SuDUi2Nt6hEZ4eu1smz
=7wjV
-END PGP SIGNATURE-


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

Re: [HACKERS] pg_dump and pg_dumpall in real life (proposal)

[Rafael Martinez]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/11/2013 11:20 PM, Josh Berkus wrote:
> On 11/11/2013 06:24 AM, Stephen Frost wrote:
>> * Rafael Martinez (r.m.guerr...@usit.uio.no) wrote:
>>> * We need a pg_dump solution that can generate in one step all
>>> the necessary pieces of information needed when restoring or
>>> cloning a database. (schema, data, privileges, users and alter
>>> database/role data)
>> 
[.]
> 
>> As for 'what we need', I'd think someone with the time and energy
>> to write the patch and work with the community to implement it..
> 
> +1
> 

Well, I am willing to take a chance on the first suggestion if nobody
else has the time or energy.

I have never sent a patch or have worked with the postgres code, but I
think it can be done without a lot of work with some reuse of the code
used in pg_dumpall.

This is a proposal based on the feedback we have received:

* pg_dump will also deliver information about "ALTER DATABASE ... SET"
data for a given database when the option '--create' is used.

* pg_dump will deliver information about ROLES used and "ALTER ROLE
... SET" data for a given database when a new option i.e.
"--roles-global" is used.

* pg_restore will restore ROLE information when used with a new option
i.e. "--roles-global" and "ALTER DATABASE ... SET" information when
used with the '--create' option.

* We need to do something with how pg_restore will handle ROLES
information because some security concerns when restoring roles that
already exists on the target server.

Some of the suggestions are:

a) Implement and use CREATE ROLE IF NOT EXISTS and just throw a
warning or handle the "role already exists" error message gracefully.

b) Use a new option i.e. "--reuse-roles-in-conflict" to behave like
suggestion a). If this option is not used, pg_restore will stop with a
fatal error when a role already exist.

c) Use a new option i.e. "--on-role-error-stop" to stop with a fatal
error when a role already exist. If this option is not used pg_restore
will behave like suggestion a).

d) Use a new option i.e. "--rename-roles-in-conflict" to rename the
roles that already exists. If this option is not used, pg_restore will
stop with a fatal error when a role already exist.

I think I prefer b) to continue with the postgres spirit of security
by default. d) is too complicated for me due to lack of knowledge of
the postgres code.

Comments?
regards,
- -- 
 Rafael Martinez Guerrero
 Center for Information Technology
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAlKB8w0ACgkQBhuKQurGihSq7QCfZdzreRGNRx0vUzXPjYqzNOIP
LqgAoJnfeCYjsfEUmsYvvp3DSL959IRL
=8Ynv
-END PGP SIGNATURE-


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

[HACKERS] pg_dump and pg_dumpall in real life

[Rafael Martinez]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hello

After some discussions in #pgconfeu, this is an attempt to relaunch
the discussion about how pg_dump and pg_dumpall work and the
challenges they give us in real life.

We have got bitten sometimes because of their behavior and we can see
it is a difficult subject for new postgres users even if they have
long experience with others databases.

Logical backups are used for restores or cloning purposes. If as a
database administrator you want to be able to do one of these
procedures for a database, you need to do this in advance today:

1) Use pg_dump to dump schema, data and privileges of the database.
2) Use pg_dumpall -g to dump global objects
3) Use pg_dumpall -g to dump ALTER ROLE ... SET ... data
4) Use pg_dumpall to dump possible ALTER DATABASE ... SET ... data

For a cloning procedure we need the samme steps but usually and in
addition we have to change the name of the owner/database when
importing the dumps.

If you have just a few and not very complicated databases in your
cluster, these steps will not be very complicated although very
irritating.

Imagine you have several hundred databases in your cluster, with
several hundred users owning some objects and with grants in others.

Imagine you are cloning or restoring only one or a few of these
databases to another server. For 2), 3) and 4) you will have to parse
the output from pg_dumpall to get the few global objects, ALTER ROLE
and ALTER DATABASE data for the few databases you are restoring.

In addition, if you have used "GRANT .. ON .. TO ..." in your database
objects you will have to take care of this manually to find out who
has extra privileges in your objects, so you can also get the right
information from pg_dumpall -g.

You don't need a lot of imagination to understand what a mess this can
be when moving data around. Not to talk about the possibility of doing
something wrong in the process and not be a very robust solution.

After many years of using pg_dump/pg_dumpall, this is our experience,
our wishes and thoughts:

* We need a pg_dump solution that can generate in one step all the
necessary pieces of information needed when restoring or cloning a
database. (schema, data, privileges, users and alter database/role data)

* It would be great to be able to tell pg_restore that user1 in the
dump will became user2 in the restored/cloned database. The same for
the name of the database.

* For serious backup management of large and complicated databases,
pg_dump with the custom output + pg_restore is the only feasible solution.

What do you think about the subject?  Does it sound like a reasonable
proposition? What do we need to implement some of these changes?

Thanks in advance for your time.

Some background information:

Ref:
http://wiki.postgresql.org/wiki/Todo
http://www.postgresql.org/message-id/4864f001.50...@archonet.com
http://www.postgresql.org/message-id/11646.1272814...@sss.pgh.pa.us

regards,
- -- 
 Rafael Martinez Guerrero
 Center for Information Technology
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAlKA4q4ACgkQBhuKQurGihSJJACglhZnjSTGFvzz6Rl0Vhrl3BrY
gssAni2l7kOQFxzr6IlDHAd0oMryDkT5
=Ti6V
-END PGP SIGNATURE-


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

[HACKERS] psql --single-transaction does not work as expected

[Rafael Martinez]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hello

I want to report that psql --single-transaction does not work as one
can expect after reading the help information for psql.

psql --help says:

- -1 ("one"), --single-transaction: execute command file as a single
transaction

If you run "psql -1 < sql_file.sql" insteed of "psql -1 -f
sql_file.sql" the single-transaction parameter will not work.


Test case:
==

CREATE DATABASE test;
\c test
CREATE TABLE check_psql(id int, code text);
\q

# cat check_psql.sql

INSERT INTO check_psql (id,code) VALUES (1,'code1');
INSERT INTO check_psql (id,code) VALUES (2,code2);

# psql -1 test -f check_psql.sql

INSERT 0 1
psql:check_psql.sql:2: ERROR:  column "code2" does not exist
LINE 1: INSERT INTO check_psql (id,code) VALUES (2,code2);
   ^
# psql -1 test -c "SELECT * FROM check_psql"
 id | code
- +--
(0 rows)

# psql -1 test < /tmp/check_psql.sql

INSERT 0 1
ERROR:  column "code2" does not exist
LINE 1: INSERT INTO check_psql (id,code) VALUES (2,code2);
   ^
#psql -1 test -c "SELECT * FROM check_psql"
 id | code
- +---
  1 | code1
(1 row)


The docs for psql at
http://www.postgresql.org/docs/current/static/app-psql.html
have the right information, " -1 --single-transaction
When psql executes a script with the -f option "

I think we should either update the psql --help information for
- --single-transaction and say that this parameter only works together
with -f or update the psql code so psql -1 < file.sql also works.

regards,
- -- 
 Rafael Martinez Guerrero
 Center for Information Technology
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAlIKPQEACgkQBhuKQurGihQaGwCggvy+Fgiw1TlseZKM8oq4U/na
cgQAnRN4sw9NHBajG57wL0P+08p6Nb3y
=cNHB
-END PGP SIGNATURE-


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

Re: [HACKERS] streaming replication breaks horribly if master crashes

[Rafael Martinez]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Heikki Linnakangas wrote:

> 
> We're not talking about a timeout for promoting standby to master. The
> problem is that the standby doesn't notice that from the master's point
> of view, the connection has been broken. Whether it's because of a
> network error or because the master server crashed doesn't matter, the
> standby should reconnect in any case. TCP keepalives are a perfect fit,
> as long as you can tune the keepalive time short enough. Where "Short
> enough" is up to the admin to decide depending on the application.
> 
>

I tested this yesterday and I could not get any reaction from the wal
receiver even after using minimal values compared to the default values  .

The default values in linux for tcp_keepalive_time, tcp_keepalive_intvl
and tcp_keepalive_probes are 7200, 75 and 9. I reduced these values to
60, 3, 3 and nothing happened, it continuous with status ESTABLISHED
after 60+3*3 seconds.

I did not restart the network after I changed these values on the fly
via /proc. I wonder if this is the reason the connection didn't die
neither with the new keppalive values after the connection was broken. I
will check this later today.

regards,
- --
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)

iEYEARECAAYFAkwZyJ4ACgkQBhuKQurGihT3kgCgn4iQkZ8YKr/nAk5/QqpwYfnc
4lsAn2CKvgeeIOon+lWRHe908hbJ+zK6
=VymH
-END PGP SIGNATURE-

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

Re: [HACKERS] streaming replication breaks horribly if master crashes

[Rafael Martinez]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Robert Haas wrote:

> 
> The first problem I noticed is that the slave never seems to realize
> that the master has gone away.  Every time I crashed the master, I had
> to kill the wal receiver process on the slave to get it to reconnect;
> otherwise it just sat there waiting, either forever or at least for
> longer than I was willing to wait.
> 

Hei Robert

I have seen two different behaviors in my tests.

a) If I crash the server , the wal receiver process will wait forever
and the only way to get it working again is to restart postgres in the
slave after the master is back online. I have not been able to get the
slave database corrupted (I am running with fsync=on).

b) If I kill all postgres processes in the master with kill -9, the wal
receiver will start trying to reconnect automatically and it will
success in the moment postgres gets startet in the master.

The only different I can see at the OS level is that in a) the
connection continues to have the status ESTABLISHED forever, and in b)
it gets status TIME_WAIT in the moment postgres is down in the master.

regards,
- --
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkwZNiMACgkQBhuKQurGihQ3CQCaAhKcLkur6MO0/F7RqD6OWbv2
R/IAnjj4SrgiwkD6qKodJxrFHCODAEuh
=qHlh
-END PGP SIGNATURE-

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

Re: [HACKERS] 9.0beta2 - server crash when using HS + SR

[Rafael Martinez]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Robert Haas wrote:
> On Fri, Jun 11, 2010 at 9:29 PM, Rafael Martinez

> I'm somewhat disinclined to try to address this for 9.0.  We've had
> this problem for a long time, and I'm not sure that the fact that it
> can now happen in a slightly wider set of circumstances is enough
> reason to engineer a solution so close to release time, nor am I sure
> what that other solution would look like.  But I'm open to other
> opinions.
> 

A minimum and probably the only feasible thing for 9.0 will be to update
the documentation. We need an entry in the hot-standby caveats section
explaining that if you create a tablespace and the directory needed does
not exist in the the standby, the standby will shutdown itself and will
not be able to start until the directory is also created in the standby.

For a DBA point of view, two possible solutions could be:

1) PostgreSQL creates the directory needed for the tablespace if the
user running postgres has privileges to do so at the OS level.

2) The standby discovers that the directory needed does not exist and
pauses the recovering (without shutting down the server) in the WAL
record that creates the tablespace. The standby will check periodically
if the directory is created before starting the recovery process again.

With this the users will be able to continue using and running queries
in the standby node. In very busy systems with many changes, the standby
will fall behind quite a lot if the error is not discovered and fixed
quickly. But in many other systems the delay will not be a problem as
serious as the loss of access to the standby.

regards,
- --
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEUEARECAAYFAkwVSsgACgkQBhuKQurGihQ1HgCXQKdwOEHLkj7g6FpJG663NUiZ
2gCZAT70aIQZ5Wj3IqsLlY6n+leLruI=
=neA1
-END PGP SIGNATURE-

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

[HACKERS] 9.0beta2 - server crash when using HS + SR

[Rafael Martinez]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hello

I am testing HS + SR in a system running 9.0beta2. What I am doing is
just trying all kind of crazy combinations and see how the system
handles them.

One of the test I knew was going to fail was to create a tablespace in
the master node with the directory used by the tablespace existing in
the master and not in the standby node.

What I didn't expect was such a serious consequence. Postgres crashed in
the standby node and it refused to start until the directory needed by
the tablespace was created also in the standby.

I suppose there is not an easy way of fixing this, but at least it would
be a good idea to update the documentation with some information about
how to fix this error situation (hot-standby.html#HOT-STANDBY-CAVEATS
will be a nice place to have this information)

Another thing is that the HINT message in the logs was a little
misleading. The server is down and it will not start without fixing the
cause of the problem.
- 
FATAL:  directory "/var/pgsql/ts_test" does not exist
CONTEXT:  xlog redo create ts: 20177 "/var/pgsql/ts_test"
LOG:  startup process (PID 10147) exited with exit code 1
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
- ----

regards,
- --
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkwS4ssACgkQBhuKQurGihQSfACePmzdjILYnPzKnk9NuDoB19YT
b3YAn2ufyis1r819ow3KJ46OO0Kv0Hd0
=boIg
-END PGP SIGNATURE-

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

Re: [HACKERS] PITR - Bug or feature?

[Rafael Martinez]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Fujii Masao wrote:
> On Mon, Feb 1, 2010 at 7:33 PM, Rafael Martinez
>  wrote:

>>
>> Any thoughts about this? Is this a bug or a 'feature'?
> 
> This is not a bug. Since pg_start_backup() uses "%X/%X" (not "%08X/%08X")
> as the format of WAL location, the length of the second number of the WAL
> location could be less than 8.
> 
> Instead of calculating the name of the backup history file for yourself,
> how about using pg_xlogfile_name() or pg_xlogfile_name_offset()? 

Thanks for the answer. We have updated our code and started using
pg_xlogfile_name() in our PITR script. Everything works perfect now.

When we started using PITR with version 8.1, we didn't have these
functions and that was the reason we were using the value returned by
pg_start_backup() to find out the last WAL to keep after PITR was finnish.

regards,
- --
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.7 (GNU/Linux)

iD8DBQFLaUXLBhuKQurGihQRAqNpAKCLCc6MDhGONJi5fTgStFoC+PP6hgCdHqVC
yDfsC1erRWxFJRCF305Bbg8=
=Brbz
-END PGP SIGNATURE-

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

[HACKERS] PITR - Bug or feature?

[Rafael Martinez]

Hello

Today we have got one PITR backup history file in one of our systems
with a format change.

The PITR backup history file named
pg_xlog/00010038.0020.backup included this information:

START WAL LOCATION: 38/20 (file 00010038)
STOP WAL LOCATION: 38/88 (file 00010038)
CHECKPOINT LOCATION: 38/20
START TIME: 2010-02-01 07:20:05 CET
LABEL:
/usit/dbpg-research/pg_bck/PITR_data/PITRBASE-dbpg-research_v8.3.9_j10_2010-02-01_072001
STOP TIME: 2010-02-01 07:22:42 CET


As you can see, the second number in START/STOP and CHECKPOINT LOCATION
has a length of 2. This second number usually has a length of 8.

I have checked the logs from our last 5000 PITR jobs and the format of
these values has always been "/<8 digits number>"

We have been using the "/<8 digits>" ID returned by the
pg_start_backup() function to find out the PITR backup history file we
have to wait for in the archive directory, before we can delete old WAL
files that are not needed anymore. This task gets more complicated if we
cannot trust to get a consistent format from pg_start_backup().

These are some thoughts that may help to debug this issue:

* The postgresql version with this 'problem' is 8.3.9
* The active WAL ID when we started and stopped PITR ends with '00'
* We have not seen this change of format in 8.3.9 when the WAL ID does
  not ends with '00'
* We have had WAL files ending with '00' with versions < 8.3.9 and the
  format used have been the expected ("/<8 digits>").

Any thoughts about this? Is this a bug or a 'feature'?

Thanks in advance.
regards,
-- 
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/

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

Re: [HACKERS] PITR backup history files with identical 2nd part file names

[Rafael Martinez]

Tom Lane wrote:
> Rafael Martinez  writes:

> 
>> All PITR backup history files created when running a PITR base backup on
>> all PostgreSQL clusters running in this new server (at different hours
>> during the night) got an identical 2nd part file name.
> 
>> <24 digits>.0020.backup e.g.000202080060.0020.backup
> 
> I think this is normal behavior now, if you have an unloaded server.
> pg_start_backup now forces a segment switch, so if nothing much else is
> happening it's quite likely that the recorded start point will be the
> beginning of the WAL segment (plus the page header size).
> 

The strange thing is that a lot is happening. These clusters generate
several hundred WAL files a day.

How is possible to get the same value *everytime* we run a base backup
with many WAL files generated between runs and on different clusters?

I trust what you say on the subject :-)  is only that in all the
years we have been using PITR, we have never seen identical values in
the 2nd part of the backup history file name (not one)

-- 
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/

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

[HACKERS] PITR backup history files with identical 2nd part file names

[Rafael Martinez]

Hello

After upgrading to 8.3.9 and moving some our PostgreSQL clusters to a
new server yesterday, we have experienced a strange thing this past night.

All PITR backup history files created when running a PITR base backup on
all PostgreSQL clusters running in this new server (at different hours
during the night) got an identical 2nd part file name.

<24 digits>.0020.backup e.g.000202080060.0020.backup

All of them got 0020.backup. as 2nd part of the file name. After
checking old logfiles from other servers, we can say that this is the
first time this happens. They used to be very different values each time
we run a PITR base backup.

We found this because a 'bug' in the script we use to create the PITR
base backup. This script could not find out the WAL files to be deleted
after one of the base backup was finished because 0020 could be
found in all WAL file names in one of the clusters. This 'bug' has been
already fixed in our script.

After some more testing, it looks like we get the same 2nd part of the
file name *everytime* we run a PITR base backup i.e. '.0020.backup'.
This happens on 10 different postgreSQL clusters runnig on the same server.

Is it normal to get the same 2nd part of the file name all the time? How
is this value generated?

This behavior is strange and I wonder if there is anything wrong with
this new server. Everything else looks ok and works without problems.

Thanks in advance.
-- 
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/

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

Re: [HACKERS] Clearing global statistics

[Rafael Martinez]

Greg Smith wrote:
> 
>> Before 8.3, we had the stats_reset_on_server_start parameter and
>> the pg_postmaster_start_time() function. This was an easy way of
>> resetting *all* statistics delivered by pg_stat_* and knowing when
>> this was done. We were able to produce stats with information about
>> sec/hours/days average values in an easy way.
>> 
> 
> With this new feature I'm submitting, you can adjust your database 
> startup scripts to make this happen again.  Start the server, 
> immediately loop over every database and call pg_stat_reset on them
> all, and call pg_stat_reset_shared('bgwriter').  Now you've got
> completely cleared stats that are within a second or two of 
> pg_postmaster_start_time(), should be close enough to most purposes.
>  Theoretically we could automate that better, but I've found it hard
> to justify working on given that it's not that difficult to handle
> outside of the database once the individual pieces are exposed.
> 


Great, this is good enough and we get what we need. Thanks :-)

regards
-- 
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/

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

Re: [HACKERS] Clearing global statistics

[Rafael Martinez]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Greg Smith wrote:
> Magnus Hagander wrote:
>> I have on my TODO to implement the ability to do stats reset on a
>> single object (say, one table only). Please take this into
>> consideration when you design/name this, so theres no unnecessary
>> overlap :-) Same goes for the stats message itself.
>>  
[.]
>
> Any comments before I update my patch to do that?
> 

Hello

One thing I miss from the statistics you can get via pg_stat_* is
information about how long we have been collecting stats (or in other
words, when was the last time the stats were reset)

Statistics without time period information are unfortunately not very
usefull for a DBA :-(

Before 8.3, we had the stats_reset_on_server_start parameter and the
pg_postmaster_start_time() function. This was an easy way of resetting
*all* statistics delivered by pg_stat_* and knowing when this was done.
We were able to produce stats with information about sec/hours/days
average values in an easy way.

I tried to discuss this some time ago but we did not get anywhere,
Ref: http://archives.postgresql.org/pgsql-general/2009-07/msg00614.php

Maybe this time? :-)

Is there any chance of implementing a way of knowing when was the last
time statistics delivered via pg_stat_* were reset?

regards,
- --
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.7 (GNU/Linux)

iD8DBQFLTx9cBhuKQurGihQRAnTZAJ9afYGu4UShAha0L6Z3OFyqgJ6SJQCffEow
sfFKKoT3ODap6JRpn2I1IfI=
=bCqY
-END PGP SIGNATURE-

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

Re: [HACKERS] Table size does not include toast size

[Rafael Martinez]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Tom Lane wrote:
> Rafael Martinez  writes:
>> I am probably missing the point here, why is it not supposed to show the
>> size of the table(data) *without* indexes?
> 
> Because pg_relation_size is defined at the "physical" level of showing
> one relation, where relation means a pg_class entry.  If you want
> agglomerations of multiple relations, you can use
> pg_total_relation_size, 


Ok, thanks for the clarification :-)

The 'problem' is that as a developer with advanced knowledge of the
postgres internals, you see a table as a group of relations (toast,
indexes, toast relation's index, etc)

A 'normal' user only sees a table and its indexes and this user
misinterpret the use of the function "pg_relation_size() when it reads
in the documentation "pg_relation_size(): Disk space used by the table
or index ... "

regards,
- --
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.7 (GNU/Linux)

iD8DBQFLL5lwBhuKQurGihQRApt1AJ4wQS9+WSiUSAB6sSV6i/z0y0gZhwCfWq1Y
BnnbddNedMMGCUGJ+X4eMMY=
=yUsa
-END PGP SIGNATURE-

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

Re: [HACKERS] Table size does not include toast size

[Rafael Martinez]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Bernd Helmle wrote:
> 
> 
> --On 21. Dezember 2009 10:01:37 -0500 Tom Lane  wrote:
> 
>> It's not supposed to.  Use pg_total_relation_size if you want a number
>> that includes index and toast space.
> 
> I've created a C-Function a while ago that extracts the TOAST size for a
> given relation. This gave me the opportunity to do a
> pg_relation_size(oid) + pg_relation_toast_size(oid) for a given table
> oid to calculate on disk data size required by a table. Maybe we should
> include such a function in core?
> 

It is a possibility. But I really think that pg_relation_size() not
reporting the total size of the table (without indexes) is useless.

toast is an internal way of organizing/saving data for tuples larger
than the page size used by PostgreSQL. It is a mechanism transparent to
the user and therefore pg_relation_size() should not differentiate
between data saved via toast or not.

The size of the table without the indexes should be reported regardless
the technique used to save the data on the disk.

regards,
- --
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.7 (GNU/Linux)

iD8DBQFLL5ZHBhuKQurGihQRAoR8AJ97RoST3VHGCmcIOhkdRbJIWb3mnwCeN7Mm
7Oja4kmyrQfM6/RxyUE4K2A=
=kxO9
-END PGP SIGNATURE-

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

Re: [HACKERS] Table size does not include toast size

[Rafael Martinez]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Tom Lane wrote:
> Rafael Martinez  writes:
>> I wonder why the function pg_relation_size(text) does not take into
>> account the space used by toast data in a table when returning the space
>> used by the table.
> 
> It's not supposed to.  Use pg_total_relation_size if you want a number
> that includes index and toast space.
> 

I am probably missing the point here, why is it not supposed to show the
size of the table(data) *without* indexes?

My question was because I can not understand the use and usefulness of
pg_relation_size() (as it works today) in a table that use toast.

- From an administrator point of view, there are two numbers that are
interesting, the total size of a table (indexes included) and the size
of the table without taking into account the space used by its indexes.

At least, if there is a logic in this behavior, it should be documented
in "9.23. System Administration Functions". The documentation only says
"Disk space used by the table or index with "

It is not the first time confused users have asked me why
pg_relation_size() does not show the space used by the table without
indexes. Many do not know what 'toast' is, and most probably they do not
need to know about this either.

regards,
- --
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.7 (GNU/Linux)

iD8DBQFLL5NCBhuKQurGihQRAmtZAJ99wJPKbS1u2RUGxO4G++X7nbqt2gCeJubn
b+328nrEICsXPS7kgD4bq68=
=bBO8
-END PGP SIGNATURE-

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

[HACKERS] Table size does not include toast size

[Rafael Martinez]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hello

I wonder why the function pg_relation_size(text) does not take into
account the space used by toast data in a table when returning the space
used by the table.

As an administrator I would expect pg_total_relation_size() to return
data+toast+indexes and pg_relation_size() to return data+toast.

Is this a deliberate decision? Could we change this behavior in the future?

We are using a 8.3 database.

Thanks in advance.
regards,
- --
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.7 (GNU/Linux)

iD8DBQFLL3m+BhuKQurGihQRAgBdAKCV5ZIBJyDOzGWh/En4sTvWSW67ZwCfYoYx
iUYIMJCbk6li2BhYcR7JB5M=
=l2YF
-END PGP SIGNATURE-

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

Re: [HACKERS] More robust pg_hba.conf parsing/error logging

[Rafael Martinez]

Alvaro Herrera wrote:
> Rafael Martinez wrote:
> 
>> Shouldn't 'all' be a reserved word?, it has a special meaning when used
>> in pg_hba.conf.
> 
> No, it works fine with a line like this:
> 
> local   "all"   all   md5
> 

Ok, then "all" and "ALL" should be valid values but not all and ALL
(without "")

regards
-- 
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/

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

Re: [HACKERS] More robust pg_hba.conf parsing/error logging

[Rafael Martinez]

Andrew Dunstan wrote:
> 
> 
> Rafael Martinez wrote:
>>
>> Or throw an error saying 'ALL' is not a valid value and *not* reload the
>> pg_hba.conf file.   
> 
> 
> But it's not invalid. It would designate a database or user named "ALL".
> That might be a silly thing to do, but that's another question.
> 

Shouldn't 'all' be a reserved word?, it has a special meaning when used
in pg_hba.conf.

regards
-- 
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/

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

[HACKERS] More robust pg_hba.conf parsing/error logging

[Rafael Martinez]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hello

The origin of this petition is an error produced today by a user on one
of our systems. Because of this error many users lost access to their
databases.

Problem:
- 
If you define in pg_hba.conf a database or a user value with 'ALL'
instead of 'all', you will lose access to *all* databases involved. The
reload process will not report anything about 'ALL' been an invalid
value and the new pg_hba.conf will be reloaded.

This is the only thing in the log file:
"LOG:  received SIGHUP, reloading configuration files"

Solution:
- -
Or change internally all uppercase to lowercase so users can define
values in pg_hba.conf with uppercase characters.

Or throw an error saying 'ALL' is not a valid value and *not* reload the
pg_hba.conf file. This is already done if you use uppercase when you
define connection type or authentication method.

regards,
- --
 Rafael Martinez, 
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.7 (GNU/Linux)

iD8DBQFKp7GVBhuKQurGihQRAhCZAJ9y5BhdWbrpJeW12g/rJ6yRfgubgACglYC3
wkG1cHESexmSZ48/Fc63vU4=
=a46y
-END PGP SIGNATURE-

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

[HACKERS] Guessing future postgresql features

[Rafael Martinez]

Hello

I am preparing a presentation about postgresql for GoOpen2008 [1] in
Norway. I am trying to guess some of the 'main' new features we could
expect to see in the next versions of postgresql.

After reading different documents on Internet, I have this list which I
plan to include in my presentation. Does anyone disagree with it?  ;-)

* Auto-tuning / auto-configuration
* Easy update-in-place - 'pgmigrator'
* More SQL99 and SQL2003 features
* Update-in-place optimizations which enhance OLTP performance
* Auto partitioning / Dynamic partitioning
* External tables interfaces (SQL/MED compliant)
* More exotic datatypes
* More query optimizer improvements
* Elimination of vacuum
* Improve XML support
* Pre-parsing phase that converts non-ISO syntax to supported syntax.

Thanks in advance for your feedback.

[1] http://friprog.no/ez/index.php?/nor/English

regards,
-- 
 Rafael Martinez, <[EMAIL PROTECTED]>
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/

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

Re: [HACKERS] PostgreSQL performance issues

[Rafael Martinez]

Deblauwe Gino wrote:

> a) I didn't see a reindex in your mail.  That's why a backup and a
> restore work and a vacuum doesn't
> http://www.postgresql.org/docs/current/static/sql-reindex.html
> Do this at least daily with that many inserts
> 

Hello

I'am sorry to say that this advice does not sound correct.

He is talking about 10,000 transactions and 15,000 new rows per
table/day. This should not require a daily reindex of 12 indexes. It has
to be something else and more information will help to find the problem.

1GB ram sounds maybe to little for a 40GB+ database. It is difficult to
say more without knowing more about the EXPLAIN output for the selects
with problems. It would be good too to know some of the memory values in
postgresql.conf.

I can count with one hand the times that I have had to run a reindex to
fix something in the past years. We have databases with 9millons+
transactions and 2millons+ inserts/updates a day and we have never had
to run a reindex to get a good performance

PS.- RAID-0 for a database is a disaster waiting to happen.

regards
-- 
 Rafael Martinez, <[EMAIL PROTECTED]>
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] Feature Freeze date for 8.4

[Rafael Martinez]

Tatsuo Ishii wrote:

> 
> +1. Shorter release cycles are maybe good for fancy GUI oriented
> applications, but not so good for DBMS.
> --

I agree, sure it will be great to have even more and new features as
soon as possible, but not if the quality of the final product decrease.
The most important characteristics of postgresql are stability,
,reliability and performance. It just works. We should not forget this.

And as others have said, from a DBA and system administrator
perspective, maintaining large installations updated in a 6 months
release cycle will be a nightmare.

We are always 1 year back the main release. We are testing and planing
the move to 8.2 now, and it won't happen until desember. In a 6 month
cycle we will have to jump over every second release.

regards
-- 
 Rafael Martinez, <[EMAIL PROTECTED]>
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] [GENERAL] 8.1.4 - problem with PITR - .backup.done /

[Rafael Martinez]

On Tue, 2006-05-30 at 15:38 -0400, Tom Lane wrote:
[...]
> 
> My thought is that the stat()s on the .done file failed for some obscure
> reason, perhaps insufficient kernel resources, even though the file was 
> actually there.
> 
> If you have postmaster log output for the interval in which this
> happened, it would be interesting to look for occurrences of this
> warning message from pgarch_archiveDone:
> 
> if (rename(rlogready, rlogdone) < 0)
> ereport(WARNING,
> (errcode_for_file_access(),
>  errmsg("could not rename file \"%s\" to \"%s\": %m",
> rlogready, rlogdone)));
> 
> If you find any then we might need a different theory ...
> 

I do not find any warning message "could not rename file ...". These are
the relevant entries in the log file:


[2006-05-29 17:31:55.212 CEST]   12022 LOG:  archived transaction log
file "00010008000F"

 PITR_basebackup script started around 17:32 

[2006-05-29 17:40:27.735 CEST]   12022 LOG:  archived transaction log
file "000100080010"
[2006-05-29 17:49:32.075 CEST]   12022 LOG:  archived transaction log
file "000100080011"
[2006-05-29 17:59:40.575 CEST]   12022 LOG:  archived transaction log
file "000100080012"
[2006-05-29 18:08:27.229 CEST]   12022 LOG:  archived transaction log
file "000100080013"
[2006-05-29 18:11:36.434 CEST]   12022 LOG:  archived transaction log
file "000100080010.0006D5E8.backup"

[2006-05-29 18:11:36.467 CEST]   12022 LOG:  archive command
"archive_wal.sh -P pg_xlog/000100080010.0006D5E8.backup -F
000100080010.0006D5E8.backup" failed: return code 256

[2006-05-29 18:11:37.479 CEST]   12022 LOG:  archive command
"archive_wal.sh -P pg_xlog/000100080010.0006D5E8.backup -F
000100080010.0006D5E8.backup" failed: return code 256

[2006-05-29 18:11:38.492 CEST]   12022 LOG:  archive command
"archive_wal.sh -P pg_xlog/000100080010.0006D5E8.backup -F
000100080010.0006D5E8.backup" failed: return code 256

[2006-05-29 18:11:38.492 CEST]   12022 WARNING:  transaction log file
"000100080010.0006D5E8.backup" could not be archived: too
many failures

 PITR_basebackup script finnished 18:12:16 
...
 Same error several times until we deleted the .backup.ready file at
18:15 

[2006-05-29 18:19:14.546 CEST]   12022 LOG:  archived transaction log
file "000100080014"
[2006-05-29 18:30:10.939 CEST]   12022 LOG:  archived transaction log
file "000100080015"
...


Our PITR_basebackup script does this:

* Checks if Backup label file exists
* Starts Backup process with pg_start_backup()
* Creates a LVM2 Snapshot of data partition
* Mounts the Snapshot partition
* Creates a tar.bz2 file of data
* Umounts Snapshot partition
* Removes Snapshot LV
* Backup last WAL file not yet archived
* Stops Backup process with pg_stop_backup()
* Waits for *.backup file to appear under the archivedir 
* Removes old WAL archived files
* Removes old tar.bz2 data file

To create the tar.bz file and to delete old WAL files can take some
time. The total running time of the PITR_basebackup script was 2412 sec.

If we get the same problem again, I will try to get more information
from the system. As I said in my last e-mail, this has been a one time
problem.

regards,
-- 
Rafael Martinez, <[EMAIL PROTECTED]>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] [GENERAL] 8.1.4 - problem with PITR - .backup.done /

[Rafael Martinez]

On Tue, 2006-05-30 at 09:45 -0400, Tom Lane wrote:
> "Rafael Martinez, Guerrero" <[EMAIL PROTECTED]> writes:
> > The problem was that 000100080010.0006D5E8.backup was
> > already archived, but under pg_xlog/archive_status/ there were two
> > files:
> > -
> > 000100080010.0006D5E8.backup.done
> > 000100080010.0006D5E8.backup.ready
> > -
> 
> > This situation should not happen, anyone has seen this problem before?
> 
> No, it shouldn't.  What I suspect is that XLogArchiveIsDone() got
> confused and created a duplicate .ready file.  It basically assumes
> that the only way its stat() calls can fail is ENOENT, ie, file not
> there ... but I wonder if they failed for some other reason instead.
> What sort of platform and filesystem is this on?
> 

This is on a AMD64 Opteron server with RHELAS4 / 2.6.9-34.ELsmp and ext3
filesystem. This is the first time this happens.

I cannot the postgres internals but after a quick look to the source
code ..

XLogArchiveIsDone() has this code in the final of the function:
-
 /* Race condition --- maybe archiver just finished, so recheck */
StatusFilePath(archiveStatusPath, xlog, ".done");
if (stat(archiveStatusPath, &stat_buf) == 0)
return true;

/* Retry creation of the .ready file */
XLogArchiveNotify(xlog);
return false;
}
-

What happens if we have a race condition and the archiver creates
a .done file between the last check for the .done file and the creation
of the .ready file by XLogArchiveNotify?

> Did you happen to make note of the mod times of the two files before
> deleting them?
> 

No, I did not :( If it happens again, I will do.

regards,
-- 
Rafael Martinez, <[EMAIL PROTECTED]>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] [GENERAL] Weird ..... (a=1 or a=2) <> (a=2 or a=1)

[Rafael Martinez]

On Fri, 2006-05-19 at 11:34 -0400, Tom Lane wrote:
[...]
> 
> This only affects the 7.4 and 8.0 branches, because earlier and later
> versions of Postgres don't use this technique for detecting duplicates.
> But it's surprising we didn't find it before.
> 
> Patches will appear in next week's releases.  Thanks again!
> 

Thanks to you for finding and fixing the problem :-)
It looks like you are finish so I will update the server and you will
lose access to it.

regards
-- 
Rafael Martinez, <[EMAIL PROTECTED]>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/


---(end of broadcast)---
TIP 6: explain analyze is your friend

[HACKERS] textToQualifiedNameList second parameter

[Rafael Martinez Guerrero]

Hello

In version 7.4.x, the  function textToQualifiedNameList was defined with
two parameters. 

Some months ago, the second parameter was removed [1] so I had to modify
my program to work after this change. Now with 8.0.7, the second
parameter is back again and I get this error when I try to compile:

pg_dbsize.c: In function `relation_size':
pg_dbsize.c:295: too few arguments to function `textToQualifiedNameList'
make: *** [pg_dbsize] Error 1

Is the second parameter back again?  

[1]: http://archives.postgresql.org/pgsql-patches/2005-05/msg00307.php

-- 
Rafael Martinez, <[EMAIL PROTECTED]>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/


---(end of broadcast)---
TIP 6: explain analyze is your friend

[HACKERS] Problems with initdb 8.0.1

[Rafael Martinez Guerrero]

Hello

I have a problem running initdb 8.0.1. I get this error message when I
try to run this command in my system:

---
-bash-2.05b$ /local/opt/postgresql/bin/initdb 
The program "postgres" is needed by initdb but was not found in the
same directory as
"/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL 
PROTECTED]".
Check your installation.
---

strace shows this:
---
..
..
getcwd("/", 1024)   = 10
chdir("/local/opt/postgresql/bin")  = 0
lstat64("initdb", {st_mode=S_IFLNK|0777, st_size=82, ...}) = 0
readlink("initdb",
"/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL 
PROTECTED]", 1024) = 82
chdir("/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin") =
0
lstat64("[EMAIL PROTECTED]", {st_mode=S_IFREG|0755, st_size=47506,
...}) = 0
getcwd("/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin",
1024) = 73
chdir("/")  = 0
write(2, "The program \"postgres\" is needed"..., 209The program
"postgres" is needed by initdb but was not found in the
same directory as
"/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL 
PROTECTED]".
Check your installation.
) = 209
exit_group(1)   = ?
---

Some additional information:

We are running a system for administration of third party software on
UNIX computers (store). With this system we can compile in a master
server versions for different platforms and different versions of the
software for a platform, distribution happens automatic.

This system is well tested and works without a problem. We have been
running the last 8-9 version of postgres in this system without a
problem.

What the system does is to create a symblink to the version for your
machine (among other things).

For example:

If we install the binaries for postgres under /local/opt/postgresql/bin,
initdb in this directory will be a symblink to
"/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL PROTECTED] 
and 

/local/opt/postgresql/bin/postgres will we a symblink to
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL PROTECTED]

if we are in a linux machine.

If we run the same version in a solaris machine:
/local/opt/postgresql/bin/initdb -->
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL PROTECTED]

and 

/local/opt/postgresql/bin/postgres -->
/local/store/bbking/.postgresql/ver-8.0.1/opt/postgresql/bin/[EMAIL PROTECTED]

Solution to the problem?:
-

I solution to this problem will be to look for postgres under the same
directory of initdb (/local/opt/postgresql/bin in our example) and not
under the directory of the initdb symblink target. 

Any comment to this?
Thanks for your time.

-- 
 Rafael Martinez, <[EMAIL PROTECTED]>
 Center for Information Technology Services
 University of Oslo, Norway



signature.asc
Description: This is a digitally signed message part