Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#10322 closed Bug/Something is broken (fixed)

New postgreSQL database on ossie

Reported by: https://id.mayfirst.org/gripuqam Owned by: https://id.mayfirst.org/jamie
Priority: Medium Component: Tech
Keywords: ossie.mayfirst.org, postgresSQL, GRIP-UQAM Cc:
Sensitive: no

Description

I need a new postgreSQL database on ossie for the UNIX user ladepense-carrefouralim-webadmin.

postgres@ossie:~$ createuser -D -R -S ladepense-carrefouralim-webadmin
postgres@ossie:~$ createdb -O ladepense-carrefouralim-webadmin ladepense-carrefouralimentaire

By the way, I am very curious to know if ossie and chelsea have two completely independent postgres servers?

Change History (8)

comment:1 Changed 4 years ago by https://id.mayfirst.org/dskallman

  • Keywords ossie.mayfirst.org postgresSQL added
  • Owner set to https://id.mayfirst.org/jamie
  • Status changed from new to assigned

I'm connecting Jamie to help with this.

comment:2 Changed 4 years ago by https://id.mayfirst.org/jamie

  • Resolution set to fixed
  • Status changed from assigned to feedback

All set (thanks for including the commands for easier reference :).

And yes - ossie and chelsea are two different virtual guests that happen to be on two different physical servers in two different colo cabinets.

With the exception of virtual guests that share the same physical hardware, our infrastructure design is intended to avoid any single points of failure. For the most part, each of our hosts can operate independently of the others.

Let us know if you have any problems with the postgres database or any other questions!

jamie

comment:3 Changed 4 years ago by https://id.mayfirst.org/gripuqam

  • Resolution fixed deleted
  • Status changed from feedback to assigned

Thank you very much!

Including the commands was a suggestion from dkg, thanks to him :).

The question about the independence between chelsea and ossie posgres servers was because I wanted to know if the same database name would refer to the same database, but I understand it would refer to completely different DB. Thanks for your explanations.

There is a bug with the new database. It is for a Drupal7 website and drupal complained about the DB not being UTF8. I was surprised because dkg told me UTF8 was the default https://support.mayfirst.org/ticket/7841#comment:31 that's why I did'n ask for it this time. I was also surprised because

ladepense-carrefouralim-webadmin@ossie:/$ psql ladepense-carrefouralimentaire
psql (9.1.14)
Type "help" for help.

ladepense-carrefouralimentaire=> \encoding
UTF8

psql \encoding command reports UTF8.

I was again surprised because I installed a drupal7 website on chelsea using postgres and it works very good.

I searched more and tried the \l psql command, it gives different result on chelsea and ossie and reports the newly created DB as SQL_ASCII, I have the impression this could explain the problem. I am not sure about the best way to fix the problem. In an ideal world ossie and chelsea should share posgres config in my opinion, if they really is a difference. The quickest way maybe to suppress the DB and recreate it with --encoding=UTF8 but I am not sure it would work.

ladepense-carrefouralim-webadmin@ossie:/$ psql ladepense-carrefouralimentaire
psql (9.1.14)
Type "help" for help.

ladepense-carrefouralimentaire=> \d
No relations found.
ladepense-carrefouralimentaire=> \l
                                                    List of databases
              Name              |              Owner               | Encoding  | Collate | Ctype |   Access privileges   
--------------------------------+----------------------------------+-----------+---------+-------+-----------------------
 apcwomen_media                 | apcwomen                         | UTF8      | C       | C     | 
 ladepense-carrefouralimentaire | ladepense-carrefouralim-webadmin | SQL_ASCII | C       | C     | 
 postgres                       | postgres                         | SQL_ASCII | C       | C     | 
 template0                      | postgres                         | SQL_ASCII | C       | C     | =c/postgres          +
                                |                                  |           |         |       | postgres=CTc/postgres
 template1                      | postgres                         | SQL_ASCII | C       | C     | =c/postgres          +
                                |                                  |           |         |       | postgres=CTc/postgres
(5 rows)
sosterritoireweb@chelsea:/home/members/gripuqam/sites/sosterritoire.gripuqam.mayfirst.org/web$ psql gripuqam-sosterritoire
psql (9.1.14)
Saisissez « help » pour l'aide.

gripuqam-sosterritoire=> \l
                                            Liste des bases de données
          Nom           |    Propriétaire     | Encodage | Collationnement | Type caract. |    Droits d'accès     
------------------------+---------------------+----------+-----------------+--------------+-----------------------
 gripuqam               | gripuqam            | UTF8     | en_US.UTF-8     | en_US.UTF-8  | 
 gripuqam-sosterritoire | gripuqam-postgresql | UTF8     | en_US.UTF-8     | en_US.UTF-8  | 
 gripuqamweb-helpdesk   | essais              | UTF8     | en_US.UTF-8     | en_US.UTF-8  | =Tc/essais           +
                        |                     |          |                 |              | essais=CTc/essais
 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  | postgres=CTc/postgres+
                        |                     |          |                 |              | =c/postgres
(6 lignes)

On chelsea the DB gripuqam-sosterritoire is used by drupal 7 and it works very good, I have the feeling it is faster than it would be with mysql.

Maybe a way to drop the DB and recreate it would be (I have not tested the commands):

postgres@ossie:~$ psql ladepense-carrefouralimentaire --command="\l"
postgres@ossie:~$ dropdb ladepense-carrefouralimentaire
postgres@ossie:~$ createdb -O ladepense-carrefouralim-webadmin --encoding=UTF8 ladepense-carrefouralimentaire
postgres@ossie:~$ psql ladepense-carrefouralimentaire --command="\l"

and check on the screen if the encoding is now UTF8.

comment:4 Changed 4 years ago by https://id.mayfirst.org/gripuqam

  • Keywords GRIP-UQAM added

comment:5 Changed 4 years ago by https://id.mayfirst.org/jamie

Sorry for the delay (long weekend here in the US). I'm examining this now.

comment:6 Changed 4 years ago by https://id.mayfirst.org/jamie

  • Resolution set to fixed
  • Status changed from assigned to feedback

Ok - it should be utf8 now:

postgres@ossie:~$ psql ladepense-carrefouralimentaire --command="\l"
                                                        List of databases
              Name              |              Owner               | Encoding  |  Collate   |   Ctype    |   Access privileges   
--------------------------------+----------------------------------+-----------+------------+------------+-----------------------
 apcwomen_media                 | apcwomen                         | UTF8      | C          | C          | 
 ladepense-carrefouralimentaire | ladepense-carrefouralim-webadmin | UTF8      | en_US.UTF8 | en_US.UTF8 | 
 postgres                       | postgres                         | SQL_ASCII | C          | C          | 
 template0                      | postgres                         | UTF8      | en_US.UTF8 | en_US.UTF8 | =c/postgres          +
                                |                                  |           |            |            | postgres=CTc/postgres
 template1                      | postgres                         | UTF8      | en_US.UTF8 | en_US.UTF8 | 
(5 rows)

postgres@ossie:~$ 

I think the problem is that the template databases were US_ASCII. So, I followed the steps here:

https://techjourney.net/convert-postgresql-template0-template1-encoding-to-utf8-sql_ascii-incompatible/

Except for the last step (dropping and re-creating the cluser). In other words:

update pg_database set datallowconn = TRUE where datname = 'template0';
\c template0
update pg_database set datistemplate = FALSE where datname = 'template1';
drop database template1;
create database template1 with encoding = 'UTF-8' lc_collate = 'en_US.UTF8' lc_ctype = 'en_US.UTF8' template = template0;
update pg_database set datistemplate = TRUE where datname = 'template1';
\c template1
update pg_database set datallowconn = FALSE where datname = 'template0';
update pg_database set encoding = 6, datcollate = 'en_US.UTF8', datctype = 'en_US.UTF8' where datname = 'template0';
update pg_database set encoding = 6, datcollate = 'en_US.UTF8', datctype = 'en_US.UTF8' where datname = 'template1';

Then, I dropped and re-created your database (but without specifying utf8 on the command line).

Please re-open if you notice any problems and sorry again for the slow response.

comment:7 Changed 4 years ago by automatic

  • Status changed from feedback to closed

No news is good news (we hope)! Given the lack of feedback, we think this ticket can be closed.

comment:8 Changed 4 years ago by https://id.mayfirst.org/gripuqam

It can, it works perfect now.

Please login to add comments to this ticket.

Note: See TracTickets for help on using tickets.