[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