A method to re-create Nova PostgreSQL database with UTF8 encoding ================================================================= This assumes an existing DevStack instance. Problem ------- By default, DevStack explicitly creates Nova database with LATIN1 encoding (refer 'Additional info' further below for DevStack output), e.g. $ sudo -u postgres psql postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- glance | root | UTF8 | C | C | keystone | root | UTF8 | C | C | neutron | root | UTF8 | C | C | nova | root | LATIN1 | C | C | 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 (7 rows) However Nova needs UTF8 encoding. This notes tries to describe to fix this retroactively, so that Nova datbase make use of UTF-8. Procedure --------- 1. Extract PostgreSQL database contents of Nova databse into a file (pg_dump) $ sudo pg_dump nova > nova-pg-db-dump 2. Kill the DevStack screen session: $ kill -9 $PID-of-screen-session Because (refer the output of this database activity in 'Additional info'): $ sudo psql -h127.0.0.1 -Uroot -dtemplate1 -c 'DROP DATABASE IF EXISTS nova' Password for user root: ERROR: database "nova" is being accessed by other users DETAIL: There are 11 other sessions using the database. 3. Drop the Nova database successfully: $ sudo psql -h127.0.0.1 -Uroot -dtemplate1 -c 'DROP DATABASE IF EXISTS nova' Password for user root: DROP DATABASE 4. Create new database with the different encoding: $ sudo createdb -h 127.0.0.1 -Uroot -l C -T template0 -E utf8 nova Password: $ echo $? 0 5. Check if the database is created with correct encoding: $ sudo -u postgres psql postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- glance | root | UTF8 | C | C | keystone | root | UTF8 | C | C | neutron | root | UTF8 | C | C | nova | root | UTF8 | C | C | 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 (7 rows) 6. Reload your data: $ sudo psql nova < nova-pg-db-dump $ echo $? 0 7. Connect back to your DevStack instance: $ ./rejoin-stack.sh 8. Ensure the ecoding for Nova reflects correctly: $ sudo -u postgres psql postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- glance | root | UTF8 | C | C | keystone | root | UTF8 | C | C | neutron | root | UTF8 | C | C | nova | root | UTF8 | C | C | 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 (7 rows) Additional info --------------- [a] From devstack.log: [. . .] 2014-11-26 14:21:18.263 | + recreate_database nova latin1 2014-11-26 14:21:18.263 | + local db=nova 2014-11-26 14:21:18.263 | + local charset=latin1 2014-11-26 14:21:18.263 | + recreate_database_postgresql nova latin1 2014-11-26 14:21:18.263 | + local db=nova 2014-11-26 14:21:18.263 | + local charset=latin1 2014-11-26 14:21:18.263 | + psql -h127.0.0.1 -Uroot -dtemplate1 -c 'DROP DATABASE IF EXISTS nova' 2014-11-26 14:21:18.273 | NOTICE: database "nova" does not exist, skipping 2014-11-26 14:21:18.273 | DROP DATABASE 2014-11-26 14:21:18.275 | + createdb -h 127.0.0.1 -Uroot -l C -T template0 -E latin1 nova 2014-11-26 14:21:20.464 | + /usr/bin/nova-manage db sync [. . .] [b] Output of activity for Nova dabatase: $ sudo -u postgres psql $ postgres=# SELECT * FROM pg_stat_activity WHERE datname='nova'; datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_ start | query_start | state_change | waiting | state | query -------+---------+-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+------ ------+-------------------------------+-------------------------------+---------+-------+-------- 18837 | nova | 15134 | 16384 | root | | 127.0.0.1 | | 38930 | 2014-11-26 10:22:33.194392-05 | | 2014-11-26 10:29:33.416675-05 | 2014-11-26 10:29:33.416769-05 | f | idle | COMMIT 18837 | nova | 15132 | 16384 | root | | 127.0.0.1 | | 38928 | 2014-11-26 10:22:33.04199-05 | | 2014-11-26 10:29:33.301006-05 | 2014-11-26 10:29:33.302259-05 | f | idle | COMMIT 18837 | nova | 15131 | 16384 | root | | 127.0.0.1 | | 38927 | 2014-11-26 10:22:33.029413-05 | | 2014-11-26 10:29:43.324895-05 | 2014-11-26 10:29:43.324962-05 | f | idle | COMMIT 18837 | nova | 15133 | 16384 | root | | 127.0.0.1 | | 38929 | 2014-11-26 10:22:33.101921-05 | | 2014-11-26 10:29:33.409427-05 | 2014-11-26 10:29:33.409535-05 | f | idle | COMMIT 18837 | nova | 15139 | 16384 | root | | 127.0.0.1 | | 38933 | 2014-11-26 10:22:33.612851-05 | | 2014-11-26 10:29:33.921805-05 | 2014-11-26 10:29:33.921894-05 | f | idle | COMMIT 18837 | nova | 15135 | 16384 | root | | 127.0.0.1 | | 38931 | 2014-11-26 10:22:33.222607-05 | | 2014-11-26 10:29:33.491746-05 | 2014-11-26 10:29:33.491827-05 | f | idle | COMMIT 18837 | nova | 15114 | 16384 | root | | 127.0.0.1 | | 38926 | 2014-11-26 10:22:27.940859-05 | | 2014-11-26 10:29:33.56972-05 | 2014-11-26 10:29:33.569764-05 | f | idle | COMMIT 18837 | nova | 15136 | 16384 | root | | 127.0.0.1 | | 38932 | 2014-11-26 10:22:33.327339-05 | | 2014-11-26 10:29:33.567904-05 | 2014-11-26 10:29:33.56799-05 | f | idle | COMMIT 18837 | nova | 15148 | 16384 | root | | 127.0.0.1 | | 38934 | 2014-11-26 10:22:36.041886-05 | | 2014-11-26 10:29:36.357024-05 | 2014-11-26 10:29:36.35876-05 | f | idle | COMMIT 18837 | nova | 15154 | 16384 | root | | 127.0.0.1 | | 38935 | 2014-11-26 10:22:39.498557-05 | | 2014-11-26 10:29:39.818759-05 | 2014-11-26 10:29:39.820557-05 | f | idle | COMMIT 18837 | nova | 6813 | 16384 | root | | 127.0.0.1 | | 38923 | 2014-11-26 09:34:31.353349-05 | | 2014-11-26 10:29:43.299793-05 | 2014-11-26 10:29:43.301705-05 | f | idle | COMMIT (11 rows)