Postgres desk: Postgresql: Porting database into postgres, rename and recreating it.

In this exercise, I accidentally ported my database into postgres default database. I attempted to rename it to something else, then I cannot logon with postgres because the postgres database no longer there. Along the way, I learned something from trial and error.

I ported my database to postgres where I have forgotten to change the user.

postgres=# create database corkdb;
CREATE DATABASE
postgres=# create user corkuser with password 'password';
CREATE ROLE
postgres=# grant all privileges on database corkdb to corkuser;
GRANT
postgres=# \i outputfile.sql

Showing all the objects been imported to my postgres database!
corkdb=# select datname, pg_database_size(datname)/1024/1024 as size_in_Megabytes from pg_database order by 2 desc;
datname | size_in_megabytes
-----------+-------------------
postgres |              1425
vcdb      |                41
aurora    |                23
template0 |                 6
template1 |                 6
corkdb    |                 6
(6 rows)

Session is locking up my postgres database

postgres=# \c vcdb
psql (8.4.20, server 9.2.9)
WARNING: psql version 8.4, server version 9.2.
         Some psql features might not work.
You are now connected to database "vcdb".
vcdb=# alter database postgres rename to corkdb;
ERROR: database "postgres" is being accessed by other users
DETAIL: There is 1 other session using the database.

Tracking down the offending session

vcdb=# select * from pg_stat_activity where datname='postgres';
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port |         ba
ckend_start         | xact_start |          query_start          |         state_change          | waiting | state |
                                query
-------+----------+------+----------+----------+------------------+-------------+-----------------+-------------+-----------
--------------------+------------+-------------------------------+-------------------------------+---------+-------+--------
------------------------------------------------------------------------------
12870 | postgres | 9103 |       10 | postgres |                  |             |                 |          -1 | 2015-05-22
11:59:03.168286-04 |            | 2015-05-22 12:04:34.236271-04 | 2015-05-22 12:04:34.237251-04 | f       | idle | ALTER D
EFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA vpx GRANT ALL ON TABLES TO vc;
(1 row)

<this version does not have pg_terminate function>
kill -15 9103

Successfully renaming the postgres database. Now, I need to re-create the postgres database shell.

vcdb=# alter database postgres rename to corkdb;
ALTER DATABASE

vcdb=# \l
                                        List of databases
   Name    |    Owner     | Encoding | Collation |    Ctype    |       Access privileges
-----------+--------------+----------+-------------+-------------+-------------------------------
aurora    | aurora_admin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | aurora_admin=CTc/aurora_admin
                                                                 : =Tc/aurora_admin
                                                                 : aurora_app=CTc/aurora_admin
corkdb    | 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 | postgres=CTc/postgres
                                                                 : aurora=CTc/postgres
                                                                 : =c/postgres
vcdb      | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
                                                                 : postgres=CTc/postgres
                                                                 : vc=CTc/postgres
(5 rows)

Creating the postgres database within psql will not create the database.

Has to create the postgres database with "createdb" command instead within the psql

Postgres desk

Friday, May 22, 2015

Postgresql: Porting database into postgres, rename and recreating it.

No comments:

Post a Comment