[Postgres-xl-general] Can't drop database
Mason Sharp
msharp at translattice.com
Mon Aug 4 09:29:22 PDT 2014
On Mon, Aug 4, 2014 at 12:03 PM, Aaron Jackson <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]
> *Sent:* Monday, August 04, 2014 10:35 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 11:24 AM, Aaron Jackson <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]
>> *Sent:* Monday, August 04, 2014 10:12 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 11:05 AM, Aaron Jackson <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
>>> *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]
>>> *Sent:* Wednesday, July 30, 2014 4:47 PM
>>> *To:* Aaron Jackson
>>> *Cc:* 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>
>>> 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
>>>> 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/672cd99b/attachment.htm>
More information about the postgres-xl-general
mailing list