#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    25
    Rep Power
    0

    Deleted Database still appears in List database names


    Hello Everyone,

    I have deleted a database "ispdb_ela2g" from postgresql, still when i do \l the database name still appears in the list .

    I want that this database "ispdb_ela2g" should be removed and deleted completely from the machine, so that it does not appears any where.

    postgres=# \l
    List of databases
    Name | Owner | Encoding | Collate | Ctype | Access privileges
    -------------+-----------+----------+-------------+-------------+-----------------------
    ispdb | ispdbuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    ispdb_ela2g | ispdbuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    ispdb_ela4g | ispdbuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
    | | | | | postgres=CTc/postgres
    template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
    | | | | | postgres=CTc/postgres
    (6 rows)

    Thank You to everyone in Advance
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,803
    Rep Power
    352
    So how exactly did you "delete" that database? Maybe you forgot the terminating ; for the drop database statement? If that is the case psql will wait for you to finish the statement - which you never did as you then entered the \l command

    SQL statements need to be terminated with a semicolon ; which is clearly documented in the manual
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    25
    Rep Power
    0
    the database has been deleted with proper semicolon at last , still the database appears with \l list of database . the name of database is ispdb_ela2g . when ever we try to delete the database it says ,

    "The database subdirectory "pg_tblspc/1594480919/PG_9.2_201204301/1594535808" is missing"

    though in this directory pg_tblspc there are no 1594480919
    directory

    root@itk-iv-child1a /tmp #
    root@itk-iv-child1a /tmp # ll /var/lib/pgsql/9.2/data/pg_tblspc
    total 0
    lrwxrwxrwx 1 postgres postgres 28 May 29 2014 16387 -> /var/opt/ericsson/itk/pgdata
    lrwxrwxrwx 1 postgres postgres 34 May 29 2014 16756 -> /var/opt/ericsson/itk/ela4g/pgdata
    root@itk-iv-child1a /tmp #
    root@itk-iv-child1a /tmp # ll /var/lib/old-pgsql-remove/9.2/data/pg_tblspc
    total 0
    lrwxrwxrwx 1 postgres postgres 28 Feb 13 2014 16387 -> /var/opt/ericsson/itk/pgdata
    lrwxrwxrwx 1 postgres postgres 34 Feb 13 2014 16755 -> /var/opt/ericsson/itk/iv1us/pgdata
    -rw-r--r-- 1 postgres postgres 0 Mar 16 05:44 a.tmp
    root@itk-iv-child1a /tmp #

    even if i delete this directory it appears again

    /var/opt/ericsson/itk/ela2g/
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    25
    Rep Power
    0
    I want to delete a entire row in pg_database table in postgresql

    ispdb=# SELECT oid,* from pg_database;
    oid | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfro
    zenxid | dattablespace | datacl
    ------------+-------------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+-------
    -------+---------------+-------------------------------------
    1 | template1 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 | 12783 | 1843
    775951 | 1663 | {=c/postgres,postgres=CTc/postgres}
    12783 | template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 | 12783 | 1844
    196944 | 1663 | {=c/postgres,postgres=CTc/postgres}
    12788 | postgres | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12783 | 616
    563848 | 1663 |
    16388 | ispdb | 16385 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12783 | 774
    744286 | 16387 |
    16757 | ispdb_ela4g | 16385 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12783 | 1392
    988349 | 16756 |
    1594535808 | ispdb_ela2g | 16385 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 12783 | 614
    075450 | 1594480919 |
    (6 rows)

    I want to delete the last row ispdb_ela2g . when ever i am trying to delete the row , i get this error message.

    ispdb=# delete from pg_database where oid='1594535808';
    ERROR: database is not accepting commands to avoid wraparound data loss in database "ispdb_ela2g"
    HINT: Stop the postmaster and use a standalone backend to vacuum that database.
    You might also need to commit or roll back old prepared transactions.
    ispdb=#
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,803
    Rep Power
    352
    delete from pg_database where oid='1594535808';
    This is not how you "delete" a database.
    To "delete" a database you have to use "drop database"
    Code:
    drop database ispdb_ela2g;
    Details in the manual: PostgreSQL: Documentation: 9.4: DROP DATABASE

    HINT: Stop the postmaster and use a standalone backend to vacuum that database.
    You might also need to commit or roll back old prepared transactions.
    What happened when you followed that advice?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    25
    Rep Power
    0
    it always gives this message when ever i delete the database

    ispdb=# drop database ispdb_ela2g;

    ERROR: database is not accepting commands to avoid wraparound data loss in database "ispdb_ela2g"
    HINT: Stop the postmaster and use a standalone backend to vacuum that database.
    You might also need to commit or roll back old prepared transactions.

    ispdb=#
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,803
    Rep Power
    352
    Originally Posted by ajays
    HINT: Stop the postmaster and use a standalone backend to vacuum that database.
    You might also need to commit or roll back old prepared transactions.
    So, did you follow that advice?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    25
    Rep Power
    0
    in the single user backend mode i ran this which is still giving the same messages.

    backend>
    backend> vacuum full;
    2015-03-19 10:05:17 CETWARNING: database "ispdb_ela2g" must be vacuumed within 999842 transactions
    2015-03-19 10:05:17 CETHINT: To avoid a database shutdown, execute a database-wide VACUUM in that database.
    You might also need to commit or roll back old prepared transactions.

IMN logo majestic logo threadwatch logo seochat tools logo