[Postgres-xl-general] Can't drop database

Aaron Jackson ajackson at revionics.com
Mon Aug 4 09:46:04 PDT 2014


Seems that rebuilding that coordinator was successful.  I will create a separate thread about a related item.

Aaron
________________________________
From: Mason Sharp [msharp at translattice.com]
Sent: Monday, August 04, 2014 11:29 AM
To: Aaron Jackson
Cc: postgres-xl-general at lists.sourceforge.net
Subject: Re: [Postgres-xl-general] Can't drop database




On Mon, Aug 4, 2014 at 12:03 PM, Aaron Jackson <ajackson at revionics.com<mailto:ajackson at revionics.com>> wrote:
There were no transaction reported on any coordinators or datanodes.
Would another alternative be to drop that coordinator and rebuild it?

The nice thing about coordinators is that they are fairly disposable. As long as you have a good one, you should be able to throw away a bad one, and quickly add a new one.


Aaron
________________________________
From: Mason Sharp [msharp at translattice.com<mailto:msharp at translattice.com>]
Sent: Monday, August 04, 2014 10:35 AM

To: Aaron Jackson
Cc: postgres-xl-general at lists.sourceforge.net<mailto:postgres-xl-general at lists.sourceforge.net>
Subject: Re: [Postgres-xl-general] Can't drop database




On Mon, Aug 4, 2014 at 11:24 AM, Aaron Jackson <ajackson at revionics.com<mailto:ajackson at revionics.com>> wrote:
I can just rebuild the cluster - thankfully this is just a toy cluster.  But in your experience, if this were a production box, do you think the best course of action would be to burn it and restore from backup?


I would work through it.

Run EXECUTE DIRECT against all the nodes looking for prepared transactions, and clean those up.

Aaron
________________________________
From: Mason Sharp [msharp at translattice.com<mailto:msharp at translattice.com>]
Sent: Monday, August 04, 2014 10:12 AM

To: Aaron Jackson
Cc: postgres-xl-general at lists.sourceforge.net<mailto:postgres-xl-general at lists.sourceforge.net>
Subject: Re: [Postgres-xl-general] Can't drop database




On Mon, Aug 4, 2014 at 11:05 AM, Aaron Jackson <ajackson at revionics.com<mailto:ajackson at revionics.com>> wrote:
Yeah, badly hosed.  The database shows up if I query it from the psql, however, it's completely unusable.  I attempted to connect to it and got the following:

postgres=# \c foobar
FATAL:  database "foobar" does not exist
DETAIL:  The database subdirectory "base/36981" is missing.
Previous connection kept


Had you at some point tried to issue drop database commands? Either via a coordinator or directly against nodes?

If you have not, do you have the log files and can see anything when that first started occurring?

Is there any clean way to purge this database in a way that won't disturb the other databases?


You could issue drop database via EXECUTE DIRECT, connecting to another database like "postgres", not the one you are trying to drop.



Aaron
________________________________
From: Aaron Jackson
Sent: Monday, August 04, 2014 10:01 AM
To: Mason Sharp
Cc: postgres-xl-general at lists.sourceforge.net<mailto:postgres-xl-general at lists.sourceforge.net>
Subject: RE: [Postgres-xl-general] Can't drop database

No dice Mason.  Something very wrong with the cluster at this point.

pgxc_clean indicates there are no prepared 2PC in this cluster.

Executed "select * from pg_prepared_xacts"
- 0 rows

pgsql -d postgres -c 'drop database foobar'
ERROR: database "foobar" is being accessed by other users
DETAIL: There is 1 prepared transaction using the database.

Aaron
________________________________
From: Mason Sharp [msharp at translattice.com<mailto:msharp at translattice.com>]
Sent: Wednesday, July 30, 2014 4:47 PM
To: Aaron Jackson
Cc: postgres-xl-general at lists.sourceforge.net<mailto:postgres-xl-general at lists.sourceforge.net>
Subject: Re: [Postgres-xl-general] Can't drop database




On Wed, Jul 30, 2014 at 5:09 PM, Aaron Jackson <ajackson at revionics.com<mailto:ajackson at revionics.com>> wrote:
I have a database that I apparently can't drop.  After several attempts I stopped all datanodes and coordinators, cleared the pg_log directory and then restarted them again.  Same error...
ERROR: database "dev_database" is being accessed by other users
DETAIL:   There is 1 prepared transaction using the database.
pg_stat_activity is showing nothing.  Prior to shutting down the database, I was running a script I use to initialize databases.  The script failed with the following notice which I haven't seen since I saw a corrupted system on postgres-xc.
WARNING:  unexpected EOF on datanode connection
WARNING:  failed to send xid to the node 16388
WARNING:  failed to send ABORT PREPARED command to the node 16388
WARNING:  failed to send xid to the node 16384
WARNING:  failed to send ABORT PREPARED command to the node 16384


There is an old prepared transaction. Postgres-XL will implicitly use two phase commit if a transaction involves more than one node. You can try to clean it out using the pgxc_clean utility to clean this up.  If that does not go smoothly, you can run select * from pg_prepared_xacts; on the nodes to find it manually, then ROLLBACK PREPARED for the transaction.

Were any nodes killed while transactions were occurring? That would cause this issue.



Aaron

------------------------------------------------------------------------------
Infragistics Professional
Build stunning WinForms apps today!
Reboot your WinForms applications with our WinForms controls.
Build a bridge from your legacy apps to the future.
http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
_______________________________________________
Postgres-xl-general mailing list
Postgres-xl-general at lists.sourceforge.net<mailto:Postgres-xl-general at lists.sourceforge.net>
https://lists.sourceforge.net/lists/listinfo/postgres-xl-general




--
Mason Sharp

TransLattice - http://www.translattice.com
Distributed and Clustered Database Solutions





--
Mason Sharp

TransLattice - http://www.translattice.com
Distributed and Clustered Database Solutions





--
Mason Sharp

TransLattice - http://www.translattice.com
Distributed and Clustered Database Solutions





--
Mason Sharp

TransLattice - http://www.translattice.com
Distributed and Clustered Database Solutions


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-general-postgres-xl.org/attachments/20140804/98ef3cbd/attachment.htm>


More information about the postgres-xl-general mailing list