Re: [GENERAL] configuring queries for concurrent updates

[Craig Ringer]

On 06/23/2012 02:33 PM, Robert Poor wrote:

[std_disclaimer]I'm not a DBA and I'm running PostgreSQL on a
quad-core Intel laptop.  You may read on after you stop
laughing...[/std_disclaimer]


Laughing?

You don't need to be a formally-qualified DBA to use Pg. That's half the 
point - it's focused on safe and correct behaviour and has great 
documentation so you don't need to go through an extensive training 
course just to turn it on.


I run Pg on several machines, but of all of them my quad-core 
SSD-equiped i7 laptop is by far the fastest. It's perfectly reasonable 
to run a small Pg database on very small hardware and expect it to 
perform very well.


Of course, if you're throwing millions of rows and terabytes of data 
around you'll want to have hardware to match, but Pg scales down as well 
as up.



I've written a version of UPSERT designed to import a large number of
records.  It works in two steps: it UPDATEs incumbent records that
share common keys with the new records, then INSERTs new records that
do not share common keys with the incumbent records.


That's incorrect; it's subject to several nasty races. The best article 
I've seen on this is here:


  http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

(thanks again depesz for your great articles).

--
Craig Ringer

--
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] configuring queries for concurrent updates

[Robert Poor]

Craig:

On Sun, Jun 24, 2012 at 12:06 AM, Craig Ringer  wrote:
> That [implementation of UPSERT] is incorrect; it's subject to several nasty 
> races.
> The best article I've seen on this is here:
>
>  http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

You're right -- that's a thorough and lucid note.

Heeding depesz's warning that advisory locks are not a GENERAL
solution, they're appropriate for my application: my code is the only
place where data is added to this particular table.  So advisory locks
sound like the way to go -- I'll give that a shot.

Thank you for the pointer.

- rdp

-- 
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] configuring queries for concurrent updates

[Craig Ringer]

On 06/24/2012 03:42 PM, Robert Poor wrote:

Craig:

On Sun, Jun 24, 2012 at 12:06 AM, Craig Ringer  wrote:

That [implementation of UPSERT] is incorrect; it's subject to several nasty 
races.
The best article I've seen on this is here:

  http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/


You're right -- that's a thorough and lucid note.

Heeding depesz's warning that advisory locks are not a GENERAL
solution, they're appropriate for my application: my code is the only
place where data is added to this particular table.  So advisory locks
sound like the way to go -- I'll give that a shot.


Yep, advisory locks sound like a good choice for that situation.

True predicate locking would solve this, allowing an app to SELECT ... 
FOR UPDATE records that may not yet exist. Pg doesn't do full predicate 
locking - it's slow, expensive in memory etc, hard to get right, causes 
deadlocks all over the place, and usually isn't what users want. Pg's 
SERIALIZABLE isolation does do predicate locking, but only lightweight 
ones used to detect serialization failures, not to block work from 
proceeding.



--
Craig Ringer

--
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] Feature discussion: Should syntax errors abort a transaction?

[Rafal Pietrak]

On Sat, 2012-06-23 at 12:18 +, Jasen Betts wrote:
> On 2012-06-19, Rafal Pietrak  wrote:
> 
> > And we are talking about interractive psql breaking transaction because
> > of syntax error - almost always this is a one time typo. I'd prefere it
> > to be a bit more "sloopy", then deployed SQL application (e.g.
> > non-interactive session).
> 
> possibly you could program keyboard macros to handle savepoints to
> have an easy way to recover from these errors, but if you're working on a

Yes, but again. In my own psql usage, it goes like this: "this is a
simple and easy SQL, most of it was cut/paste anyway  what could
possibly go wrong  ups". But it goes wrong in less then every 20th
or 100th time, less then once in a few months. So i don't realy feel
like pushing somebody into a development effort, that woud just slightly
enhance psql comfort of usage. I most certainly want even be cooking any
macros, as  I would forget to use it when it could be of some help.

My comment on this thread was mearly to object, that a request to allow
maintaining transaction state despite syntax error is "obviusly wrong".

> busy database keeping a transaction open whislt you think about syntax
> is going to cost perfromance for the other users.

And this is a really good point - although I do know my schemas and I
can choose appropriate moment for long hand-opened transation, mistakes
happen (well, this whole thread is about mistakes :)

Anyway, I personaly feel that psql would be more comfortable if one
could request "explicit rollback despite errors" (like by "BEGIN
INTERACTIVE").

-R


-- 
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] Hot standby streaming replication doesn't work

[Michael Nolan]

On Sun, Jun 24, 2012 at 1:57 AM, Tim Uckun  wrote:

> I am following the instructions on the wiki
>
> https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#PITR.2C_Warm_Standby.2C_Hot_Standby.2C_and_Streaming_Replication
> using the "10 minute" version of the setup.
>
>
What version of postgresql are you running?
--
Mike Nolan

[GENERAL] ODBC FDW - could a gently soul help on geting binaries?

[Edson Richter]

Hi!

I've tried (a lot, let me say) to compile ODBC FDW in Win32 and Win64 
platforms, without success at all.


Compilers tried so far:

MinGW Gcc
Win32 port of Gcc
Microsoft Visual C++ 2005 32 and 64 bits

None of them had success, with lots of all sort of errors...
I'm afraid I'm not a C coder any more, and learning new C/C++ switches 
for the new compilers is just far beyond my project path...


Could someone provide (or point me where to get) ODBC fdw binaries for 
Win32? I can provide space to keep it online so everyone can benefit of 
it...


Thanks,

Edson.


--
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] Feature discussion: Should syntax errors abort a transaction?

[Chris Travers]

On Sun, Jun 24, 2012 at 1:37 PM, Rafal Pietrak wrote:

> On Sat, 2012-06-23 at 12:18 +, Jasen Betts wrote:
> > On 2012-06-19, Rafal Pietrak  wrote:
> >
> > > And we are talking about interractive psql breaking transaction because
> > > of syntax error - almost always this is a one time typo. I'd prefere it
> > > to be a bit more "sloopy", then deployed SQL application (e.g.
> > > non-interactive session).
> >
> > possibly you could program keyboard macros to handle savepoints to
> > have an easy way to recover from these errors, but if you're working on a
>
> Yes, but again. In my own psql usage, it goes like this: "this is a
> simple and easy SQL, most of it was cut/paste anyway  what could
> possibly go wrong  ups". But it goes wrong in less then every 20th
> or 100th time, less then once in a few months. So i don't realy feel
> like pushing somebody into a development effort, that woud just slightly
> enhance psql comfort of usage. I most certainly want even be cooking any
> macros, as  I would forget to use it when it could be of some help.


> My comment on this thread was mearly to object, that a request to allow
> maintaining transaction state despite syntax error is "obviusly wrong".
>
> > busy database keeping a transaction open whislt you think about syntax
> > is going to cost perfromance for the other users.
>
> And this is a really good point - although I do know my schemas and I
> can choose appropriate moment for long hand-opened transation, mistakes
> happen (well, this whole thread is about mistakes :)
>

Additionally, I would point out that the times I have gotten into trouble
with long-running transactions delaying other users it has been functioning
queries which updated lots of rows unexpectedly slowly, not syntax errors
;-)

Best Wishes,
Chris Travers

[GENERAL] Question about EXISTS

[Excite Holidays]

Hi,

I have been making some test with EXISTS and I found I case that I do not
understand too well:

CREATE TABLE testing (
number_id serial,
number1 integer,
number2 integer
);
INSERT INTO testing (number1, number2) VALUES (1,1),(1,2),(2,3);

SELECT *
FROM testing
WHERE EXISTS (SELECT 1 FROM testing WHERE testing.number_id = number_id AND
number1 = 1);

As far I understand the documentation the select query should return row 1
and 2, but it is returning 1, 2 and 3.

Why is this happening ?

PS_ PostgreSQL 9.1.1 on i686-pc-linux-gnu, compiled by gcc (Debian
4.3.2-1.1) 4.3.2, 32-bit

Regards,
Ruben

Re: [GENERAL] Question about EXISTS

[David Johnston]

On Jun 24, 2012, at 22:19, Excite Holidays  wrote:

> Hi,
> 
> I have been making some test with EXISTS and I found I case that I do not 
> understand too well:
> 
> CREATE TABLE testing (
> number_id serial,
> number1 integer,
> number2 integer
> );
> INSERT INTO testing (number1, number2) VALUES (1,1),(1,2),(2,3);
> 
> SELECT *
> FROM testing
> WHERE EXISTS (SELECT 1 FROM testing WHERE testing.number_id = number_id AND 
> number1 = 1);
> 
> As far I understand the documentation the select query should return row 1 
> and 2, but it is returning 1, 2 and 3.
> 
> Why is this happening ?
> 
> PS_ PostgreSQL 9.1.1 on i686-pc-linux-gnu, compiled by gcc (Debian 4.3.2-1.1) 
> 4.3.2, 32-bit
> 
> Regards,
> Ruben
> 

When you reference the same table in multiple locations you really should give 
one of them an alias.  

In this case the non-table prefix number_id is resolving to the table inside 
the exists so you basically get (... WHERE TRUE and number1 = 1) in the 
sub-select and thus all rows are returned from the outer table (because the 
query inside the exists is no longer linked to the outer query).  As long as at 
least one record is returned by the standalone query all records will be 
selected in the outer query.  If no records are returned by the inner query 
then none will be returned by the outer.  Without the linkage you can never 
have a result that is a subset of the outer table.


David J.
-- 
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] Hot standby streaming replication doesn't work

[Tim Uckun]

I am using 9.1.

Apparently it's working now, it took a couple of restarts but it seems
to be going.

Thanks.

On Mon, Jun 25, 2012 at 12:57 PM, Michael Nolan  wrote:
>
>
> On Sun, Jun 24, 2012 at 1:57 AM, Tim Uckun  wrote:
>>
>> I am following the instructions on the wiki
>>
>> https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#PITR.2C_Warm_Standby.2C_Hot_Standby.2C_and_Streaming_Replication
>> using the "10 minute" version of the setup.
>>
>
> What version of postgresql are you running?
> --
> Mike Nolan

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

[GENERAL] Help, server doesn't start

[Anibal David Acosta]

We have an old postgres installation (8.3) running on windows 2003 

The server run perfect for many years but today it crash L

 

We have a very old backup so I'll try desperate to repair.

If I can't repair I want to skip the error and start server with only
healthy data, no matter if I lose some table page, entire table or even
entire database because the server has many databases inside.

 

 

Currently the database is not starting, when I try to start I got the in the
log:

 

2012-06-25 02:22:33 GMT LOG:  database system was interrupted while in
recovery at 2012-06-25 02:22:26 GMT

2012-06-25 02:22:33 GMT HINT:  This probably means that some data is
corrupted and you will have to use the last backup for recovery.

2012-06-25 02:22:33 GMT LOG:  database system was not properly shut down;
automatic recovery in progress

2012-06-25 02:22:33 GMT LOG:  redo starts at 17C/E1C42040

2012-06-25 02:22:33 GMT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"

2012-06-25 02:22:33 GMT FATAL:  the database system is starting up

 

[LAST TWO LINES REPEAT MANY TIMES]

 

2012-06-25 02:22:36 GMT LOG:  startup process (PID 1120) was terminated by
exception 0xC00D

2012-06-25 02:22:36 GMT HINT:  See C include file "ntstatus.h" for a
description of the hexadecimal value.

2012-06-25 02:22:36 GMT LOG:  aborting startup due to startup process
failure

2012-06-25 02:22:36 GMT FATAL:  the database system is starting up

2012-06-25 02:22:36 GMT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"

2012-06-25 02:22:36 GMT FATAL:  the database system is starting up

 

 

 

Re: [GENERAL] Question about EXISTS

[Excite Holidays]

That makes sense. Thanks!

On Mon, Jun 25, 2012 at 12:52 PM, David Johnston  wrote:

> On Jun 24, 2012, at 22:19, Excite Holidays  wrote:
>
> > Hi,
> >
> > I have been making some test with EXISTS and I found I case that I do
> not understand too well:
> >
> > CREATE TABLE testing (
> > number_id serial,
> > number1 integer,
> > number2 integer
> > );
> > INSERT INTO testing (number1, number2) VALUES (1,1),(1,2),(2,3);
> >
> > SELECT *
> > FROM testing
> > WHERE EXISTS (SELECT 1 FROM testing WHERE testing.number_id = number_id
> AND number1 = 1);
> >
> > As far I understand the documentation the select query should return row
> 1 and 2, but it is returning 1, 2 and 3.
> >
> > Why is this happening ?
> >
> > PS_ PostgreSQL 9.1.1 on i686-pc-linux-gnu, compiled by gcc (Debian
> 4.3.2-1.1) 4.3.2, 32-bit
> >
> > Regards,
> > Ruben
> >
>
> When you reference the same table in multiple locations you really should
> give one of them an alias.
>
> In this case the non-table prefix number_id is resolving to the table
> inside the exists so you basically get (... WHERE TRUE and number1 = 1) in
> the sub-select and thus all rows are returned from the outer table (because
> the query inside the exists is no longer linked to the outer query).  As
> long as at least one record is returned by the standalone query all records
> will be selected in the outer query.  If no records are returned by the
> inner query then none will be returned by the outer.  Without the linkage
> you can never have a result that is a subset of the outer table.
>
>
> David J.

Re: [GENERAL] Help, server doesn't start

[René Romero Benavides]

El 24/06/2012 09:53 p.m., Anibal David Acosta escribió:


We have an old postgres installation (8.3) running on windows 2003

The server run perfect for many years but today it crash L

We have a very old backup so I'll try desperate to repair.

If I can't repair I want to skip the error and start server with only 
healthy data, no matter if I lose some table page, entire table or 
even entire database because the server has many databases inside.


Currently the database is not starting, when I try to start I got the 
in the log:


2012-06-25 02:22:33 GMT LOG:  database system was interrupted while in 
recovery at 2012-06-25 02:22:26 GMT


2012-06-25 02:22:33 GMT HINT:  This probably means that some data is 
corrupted and you will have to use the last backup for recovery.


2012-06-25 02:22:33 GMT LOG:  database system was not properly shut 
down; automatic recovery in progress


2012-06-25 02:22:33 GMT LOG:  redo starts at 17C/E1C42040

2012-06-25 02:22:33 GMT LOG:  loaded library 
"$libdir/plugins/plugin_debugger.dll"


2012-06-25 02:22:33 GMT FATAL:  the database system is starting up

[LAST TWO LINES REPEAT MANY TIMES]

2012-06-25 02:22:36 GMT LOG:  startup process (PID 1120) was 
terminated by exception 0xC00D


2012-06-25 02:22:36 GMT HINT:  See C include file "ntstatus.h" for a 
description of the hexadecimal value.


2012-06-25 02:22:36 GMT LOG:  aborting startup due to startup process 
failure


2012-06-25 02:22:36 GMT FATAL:  the database system is starting up

2012-06-25 02:22:36 GMT LOG:  loaded library 
"$libdir/plugins/plugin_debugger.dll"


2012-06-25 02:22:36 GMT FATAL:  the database system is starting up



The first advice here is:

 * backup your data files before any attempt to fix the issue.

It might be a hardware failure that corrupted your data (specifically 
your transaction logs).
It might be a bug on that specific version, what is your minor release 
number?


 * As a last resort you could use pg_resetlog, it's a binary that
   resets the information in the control file and transaction logs.

http://www.postgresql.org/docs/8.3/static/app-pgresetxlog.html
but please backup your data directory first.
After that, if the server starts up successfully, perform a pg_dump / 
pg_dumpall A.S.A.P and reload your data on a new data cluster.


I'm on twitter @iCodeiExist

Re: [GENERAL] Help, server doesn't start

[Raghavendra]

On Mon, Jun 25, 2012 at 8:23 AM, Anibal David Acosta wrote:

> We have an old postgres installation (8.3) running on windows 2003 
>
> The server run perfect for many years but today it crash L
>
> ** **
>
> We have a very old backup so I’ll try desperate to repair.
>
> If I can’t repair I want to skip the error and start server with only
> healthy data, no matter if I lose some table page, entire table or even
> entire database because the server has many databases inside.
>
> **
>
Side-by, you should also consider upgrading PG versions to latest.


> **
>
> ** **
>
> Currently the database is not starting, when I try to start I got the in
> the log:
>
> ** **
>
> 2012-06-25 02:22:33 GMT LOG:  database system was interrupted while in
> recovery at 2012-06-25 02:22:26 GMT
>
> 2012-06-25 02:22:33 GMT HINT:  This probably means that some data is
> corrupted and you will have to use the last backup for recovery.
>
> 2012-06-25 02:22:33 GMT LOG:  database system was not properly shut down;
> automatic recovery in progress
>
> 2012-06-25 02:22:33 GMT LOG:  redo starts at 17C/E1C42040
>
> 2012-06-25 02:22:33 GMT LOG:  loaded library
> "$libdir/plugins/plugin_debugger.dll"
>
> 2012-06-25 02:22:33 GMT FATAL:  the database system is starting up
>
> ** **
>
> [LAST TWO LINES REPEAT MANY TIMES]
>
> ** **
>
> 2012-06-25 02:22:36 GMT LOG:  startup process (PID 1120) was terminated by
> exception 0xC00D
>
> 2012-06-25 02:22:36 GMT HINT:  See C include file "ntstatus.h" for a
> description of the hexadecimal value.
>
> 2012-06-25 02:22:36 GMT LOG:  aborting startup due to startup process
> failure
>
> 2012-06-25 02:22:36 GMT FATAL:  the database system is starting up
>
> 2012-06-25 02:22:36 GMT LOG:  loaded library
> "$libdir/plugins/plugin_debugger.dll"
>
> 2012-06-25 02:22:36 GMT FATAL:  the database system is starting up
>
> ** **
>
> **
>
Can you also paste whats there in shared_preload_libraries setting from
$PGDATA/postgresql.conf ?

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

Re: [GENERAL] Help, server doesn't start

[René Romero Benavides]

El 24/06/2012 11:30 p.m., René Romero Benavides escribió:

El 24/06/2012 09:53 p.m., Anibal David Acosta escribió:


We have an old postgres installation (8.3) running on windows 2003

The server run perfect for many years but today it crash L

We have a very old backup so I'll try desperate to repair.

If I can't repair I want to skip the error and start server with only 
healthy data, no matter if I lose some table page, entire table or 
even entire database because the server has many databases inside.


Currently the database is not starting, when I try to start I got the 
in the log:


2012-06-25 02:22:33 GMT LOG:  database system was interrupted while 
in recovery at 2012-06-25 02:22:26 GMT


2012-06-25 02:22:33 GMT HINT:  This probably means that some data is 
corrupted and you will have to use the last backup for recovery.


2012-06-25 02:22:33 GMT LOG:  database system was not properly shut 
down; automatic recovery in progress


2012-06-25 02:22:33 GMT LOG:  redo starts at 17C/E1C42040

2012-06-25 02:22:33 GMT LOG:  loaded library 
"$libdir/plugins/plugin_debugger.dll"


2012-06-25 02:22:33 GMT FATAL:  the database system is starting up

[LAST TWO LINES REPEAT MANY TIMES]

2012-06-25 02:22:36 GMT LOG:  startup process (PID 1120) was 
terminated by exception 0xC00D


2012-06-25 02:22:36 GMT HINT:  See C include file "ntstatus.h" for a 
description of the hexadecimal value.


2012-06-25 02:22:36 GMT LOG:  aborting startup due to startup process 
failure


2012-06-25 02:22:36 GMT FATAL:  the database system is starting up

2012-06-25 02:22:36 GMT LOG:  loaded library 
"$libdir/plugins/plugin_debugger.dll"


2012-06-25 02:22:36 GMT FATAL:  the database system is starting up



The first advice here is:

  * backup your data files before any attempt to fix the issue.

It might be a hardware failure that corrupted your data (specifically 
your transaction logs).
It might be a bug on that specific version, what is your minor release 
number?


  * As a last resort you could use pg_resetlog, it's a binary that
resets the information in the control file and transaction logs.

http://www.postgresql.org/docs/8.3/static/app-pgresetxlog.html
but please backup your data directory first.
After that, if the server starts up successfully, perform a pg_dump / 
pg_dumpall A.S.A.P and reload your data on a new data cluster.


I'm on twitter @iCodeiExist

and please, check and double check that the server process is not 
active, if it's not and pg_resetxlog complains that it is, check again 
and then delete the postmaster.pid file.


-

Re: [GENERAL] Help, server doesn't start

[Anibal David Acosta]

Yes, we must upgrade.

The value of the shared_preload_libraries is

 

shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll'
# (change requires restart)

 

An additional information..

When I login into the server the disk used by Postgres installation was
without space (0Bytes available).

 

Also is possible that someone (I am checking now) restart the Postgres
service in the middle of this situation

 

Right now I am making a file-level copy of the entire postgres folder in
order to run some corruption recover method

 

Thanks!

 

 

De: Raghavendra [mailto:raghavendra@enterprisedb.com] 
Enviado el: lunes, 25 de junio de 2012 12:31 a.m.
Para: Anibal David Acosta
CC: pgsql-general@postgresql.org
Asunto: Re: [GENERAL] Help, server doesn't start

 

 

On Mon, Jun 25, 2012 at 8:23 AM, Anibal David Acosta 
wrote:

We have an old postgres installation (8.3) running on windows 2003 

The server run perfect for many years but today it crash L

 

We have a very old backup so I'll try desperate to repair.

If I can't repair I want to skip the error and start server with only
healthy data, no matter if I lose some table page, entire table or even
entire database because the server has many databases inside.

 

Side-by, you should also consider upgrading PG versions to latest. 

 

 

Currently the database is not starting, when I try to start I got the in the
log:

 

2012-06-25 02:22:33 GMT LOG:  database system was interrupted while in
recovery at 2012-06-25 02:22:26 GMT

2012-06-25 02:22:33 GMT HINT:  This probably means that some data is
corrupted and you will have to use the last backup for recovery.

2012-06-25 02:22:33 GMT LOG:  database system was not properly shut down;
automatic recovery in progress

2012-06-25 02:22:33 GMT LOG:  redo starts at 17C/E1C42040

2012-06-25 02:22:33 GMT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"

2012-06-25 02:22:33 GMT FATAL:  the database system is starting up

 

[LAST TWO LINES REPEAT MANY TIMES]

 

2012-06-25 02:22:36 GMT LOG:  startup process (PID 1120) was terminated by
exception 0xC00D

2012-06-25 02:22:36 GMT HINT:  See C include file "ntstatus.h" for a
description of the hexadecimal value.

2012-06-25 02:22:36 GMT LOG:  aborting startup due to startup process
failure

2012-06-25 02:22:36 GMT FATAL:  the database system is starting up

2012-06-25 02:22:36 GMT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"

2012-06-25 02:22:36 GMT FATAL:  the database system is starting up

 

 

Can you also paste whats there in shared_preload_libraries setting from
$PGDATA/postgresql.conf ?

 

---

Regards,

Raghavendra

EnterpriseDB Corporation

Blog: http://raghavt.blogspot.com/

Re: [GENERAL] Help, server doesn't start

[Raghavendra]

On Mon, Jun 25, 2012 at 10:10 AM, Anibal David Acosta wrote:

> Yes, we must upgrade.
>
> The value of the shared_preload_libraries is
>
> ** **
>
> shared_preload_libraries =
> '$libdir/plugins/plugin_debugger.dll'   #
> (change requires restart)
>
> **
>

First and most important is take complete backup of the $PGDATA before
giving any tries.
Can you put blank in and try restarting .
Eg:-
shared_preload_libraries=''


 **
>
> An additional information….
>
> When I login into the server the disk used by Postgres installation was
> without space (0Bytes available).
>
> **
>

Thats strange... Seems some disk issue.

 **
>
> Also is possible that someone (I am checking now) restart the Postgres
> service in the middle of this situation
>

Not sure...


>  **
>
> Right now I am making a file-level copy of the entire postgres folder in
> order to run some corruption recover method
>
> **
>
Sounds good.

--Raghav

Re: [GENERAL] Help, server doesn't start

[Craig Ringer]

On 06/25/2012 12:40 PM, Anibal David Acosta wrote:


Yes, we must upgrade.

The value of the shared_preload_libraries is

shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll' # 
(change requires restart)



Change that to:

shared_preload_libraries = ''

(two single quotes, not a double quote)

It's unlikely that the PL/PgSQL debugger plugin is the issue, but since 
it's repeated in your logs, it's worth a go.


 When I login into the server the disk used by Postgres installation 
was without space (0Bytes available).



OK, so you probably do just have xlogs that're cut short. So long as 
nobody tried to "fix" the problem by deleting things out of the 
PostgreSQL data directory I expect you'll be OK.


AFAIK Pg is supposed to recover gracefully from out-of-disk situations, 
but this _is_ quite an old version.


I wonder if there are any out-of-disk tests in the Pg unit tests? It'd 
be somewhat tricky to automate testing for, but really good to do if 
it's practical. Something for my bored-weekend TODO I guess, even if 
it's just a standalone test set.


Right now I am making a file-level copy of the entire postgres folder 
in order to run some corruption recover method

Great.

--
Craig Ringer

Re: [GENERAL] pg_dump not dumping all tables

[Stefan Schwarzer]

>> Hi there,
>> 
>> I am pg_dump-ing all tables from schema public on the server
>> 
>>   /usr/local/pgsql/bin/pg_dump -U user my_database --schema=public 
>> --encoding=UTF-8 > dump.sql
>> 
>> and re-loading it via psql on my local machine.
>> 
>> But instead of having 708 tables as on the server, I end up with only 570 on 
>> my local machine.
>> 
>> When trying to search for the name of a missing table in the dump file, it 
>> is indeed not there. Owner of the table is the same as on all other tables…
>> 
>> What could that be?
> 
> Are you sure the missing tables are in the public schema?

Yes, they are indeed in the public schema.
-- 
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] pg_dump not dumping all tables

[Anthony Bull]

Out of curiosity, what happens if you dump that database without specifying
a schema - do you get all 708 tables?

On Fri, Jun 22, 2012 at 8:45 PM, Stefan Schwarzer  wrote:

> Hi there,
>
> I am pg_dump-ing all tables from schema public on the server
>
>   /usr/local/pgsql/bin/pg_dump -U user my_database --schema=public
> --encoding=UTF-8 > dump.sql
>
> and re-loading it via psql on my local machine.
>
> But instead of having 708 tables as on the server, I end up with only 570
> on my local machine.
>
> When trying to search for the name of a missing table in the dump file, it
> is indeed not there. Owner of the table is the same as on all other tables…
>
> What could that be?
>
> Thanks for any hint!
>
> Stef
> --
> 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] pg_dump not dumping all tables

[Scott Marlowe]

I'd also wonder if it shows up when he does -N public (exclude public schema)?

On Mon, Jun 25, 2012 at 12:37 AM, Anthony Bull  wrote:
> Out of curiosity, what happens if you dump that database without specifying
> a schema - do you get all 708 tables?
>
>
> On Fri, Jun 22, 2012 at 8:45 PM, Stefan Schwarzer
>  wrote:
>>
>> Hi there,
>>
>> I am pg_dump-ing all tables from schema public on the server
>>
>>       /usr/local/pgsql/bin/pg_dump -U user my_database --schema=public
>> --encoding=UTF-8 > dump.sql
>>
>> and re-loading it via psql on my local machine.
>>
>> But instead of having 708 tables as on the server, I end up with only 570
>> on my local machine.
>>
>> When trying to search for the name of a missing table in the dump file, it
>> is indeed not there. Owner of the table is the same as on all other tables…
>>
>> What could that be?
>>
>> Thanks for any hint!
>>
>> Stef
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>



-- 
To understand recursion, one must first understand recursion.

-- 
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] Error message "psql: could not connect to server: No such file or directory"

[Stefan Schwarzer]

>>> sudo su - _postgres /usr/local/pgsql-9.1/bin/initdb -U postgres -D 
>>> /usr/local/pgsql-9.1/data --encoding=UTF8 --locale=en_US
>>> sudo su - _postgres /usr/local/pgsql-9.1/bin/pg_ctl start -D 
>>> /usr/local/pgsql-9.1/data
>>> 
>>> Although I don't get an error message, I don't have the feeling that it 
>>> started the server. There is still nothing in the process list, and a 
>>> normal "psql" results in the same message as before "psql: could not 
>>> connect…"
>>> 
>>> So sorry for bothering you guys… just being really frustrated now…
>>> 
>>> If you have any ideas, please let me know… :-)
>> I just went back to the postgres documentation [1] (actually reading all 
>> google results on the error message one after the other) and tried out what 
>> was said there (adapted to my conditions):
>> 
>> sudo -u _postgres /usr/local/pgsql-9.1/bin/postgres -D 
>> /usr/local/pgsql-9.1/data
>> 
>> and suddenly postgres runs….
>> 
> 
> You really should be able to use pg_ctl though.
> 
> I'm puzzled about why you don't appear to be looking at the server error logs 
> (/usr/local/pgsql-9.1/data/pg_log) during troubleshooting. They're your main 
> source of info.

Hmm, I guess it's because I didn't think of it, I was't really aware of it 
(although I tried to use the Console Error Messages to find any errors), and 
wouldn't have been sure how to access it (as it belongs to the _postgres user). 
But then, it doesn't exist. I see pg_clog and pg_xlog...
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general