Can't upgrade to latest version psql 9.3 to 9.5


#1

Hi there, i tried upgrade discourse from the website but after updating docker as requested, the discourse update failed.

I went to my ssh to git pull / rebuild app / but i have this on my console

koan_dev@vps215774:/var/discourse$ sudo ./launcher rebuild app

WARNING: We are about to start downloading the Discourse base image
This process may take anywhere between a few minutes to an hour, depending on your network speed

Please be patient

Unable to find image 'discourse/discourse:1.3.5' locally
1.3.5: Pulling from discourse/discourse
6d28225f8d96: Pulling fs layer
980fbaeadfb6: Pulling fs layer
6b76e99b0a75: Pulling fs layer
a3ed95caeb02: Pulling fs layer
a3ed95caeb02: Waiting
6d28225f8d96: Verifying Checksum
6d28225f8d96: Download complete
a3ed95caeb02: Verifying Checksum
a3ed95caeb02: Download complete
6d28225f8d96: Pull complete
980fbaeadfb6: Verifying Checksum
980fbaeadfb6: Download complete
6b76e99b0a75: Verifying Checksum
6b76e99b0a75: Download complete
980fbaeadfb6: Pull complete
6b76e99b0a75: Pull complete
a3ed95caeb02: Pull complete
Digest: sha256:a3fffe0bc4a462c52429e4986932a1733be384f9211e7e48f14864bd9306fb53
Status: Downloaded newer image for discourse/discourse:1.3.5
Ensuring launcher is up to date
Fetching origin
Launcher is up-to-date
Stopping old container
+ /usr/bin/docker stop -t 10 app
app
cd /pups && git pull && /pups/bin/pups --stdin
Already up-to-date.
I, [2016-07-15T19:07:37.336346 #13]  INFO -- : Loading --stdin
I, [2016-07-15T19:07:37.342855 #13]  INFO -- : > mkdir -p /shared/postgres_run
I, [2016-07-15T19:07:37.348015 #13]  INFO -- :
I, [2016-07-15T19:07:37.348567 #13]  INFO -- : > chown postgres:postgres /shared/postgres_run
I, [2016-07-15T19:07:37.351492 #13]  INFO -- :
I, [2016-07-15T19:07:37.352049 #13]  INFO -- : > chmod 775 /shared/postgres_run
I, [2016-07-15T19:07:37.354660 #13]  INFO -- :
I, [2016-07-15T19:07:37.355138 #13]  INFO -- : > rm -fr /var/run/postgresql
I, [2016-07-15T19:07:37.357911 #13]  INFO -- :
I, [2016-07-15T19:07:37.358399 #13]  INFO -- : > ln -s /shared/postgres_run /var/run/postgresql
I, [2016-07-15T19:07:37.361017 #13]  INFO -- :
I, [2016-07-15T19:07:37.361479 #13]  INFO -- : > socat /dev/null UNIX-CONNECT:/shared/postgres_run/.s.PGSQL.5432 || exit 0 && echo postgres already running stop container ; exit 1
2016/07/15 19:07:37 socat[21] E connect(6, AF=1 "/shared/postgres_run/.s.PGSQL.5432", 36): No such file or directory
I, [2016-07-15T19:07:37.384540 #13]  INFO -- :
I, [2016-07-15T19:07:37.384931 #13]  INFO -- : > rm -fr /shared/postgres_run/.s*
I, [2016-07-15T19:07:37.387602 #13]  INFO -- :
I, [2016-07-15T19:07:37.387975 #13]  INFO -- : > rm -fr /shared/postgres_run/*.pid
I, [2016-07-15T19:07:37.389923 #13]  INFO -- :
I, [2016-07-15T19:07:37.390299 #13]  INFO -- : > mkdir -p /shared/postgres_run/9.5-main.pg_stat_tmp
I, [2016-07-15T19:07:37.392069 #13]  INFO -- :
I, [2016-07-15T19:07:37.392559 #13]  INFO -- : > chown postgres:postgres /shared/postgres_run/9.5-main.pg_stat_tmp
I, [2016-07-15T19:07:37.394317 #13]  INFO -- :
I, [2016-07-15T19:07:37.398020 #13]  INFO -- : File > /etc/service/postgres/run  chmod: +x
I, [2016-07-15T19:07:37.401330 #13]  INFO -- : File > /etc/runit/3.d/99-postgres  chmod: +x
I, [2016-07-15T19:07:37.404664 #13]  INFO -- : File > /root/upgrade_postgres  chmod: +x
I, [2016-07-15T19:07:37.405190 #13]  INFO -- : > chown -R root /var/lib/postgresql/9.5/main
I, [2016-07-15T19:07:37.523568 #13]  INFO -- :
I, [2016-07-15T19:07:37.524115 #13]  INFO -- : > [ ! -e /shared/postgres_data ] && install -d -m 0755 -o postgres -g postgres /shared/postgres_data && sudo -E -u postgres /usr/lib/postgresql/9.5/bin/initdb -D /shared/postgres_data || exit 0
I, [2016-07-15T19:07:37.526081 #13]  INFO -- :
I, [2016-07-15T19:07:37.526340 #13]  INFO -- : > chown -R postgres:postgres /shared/postgres_data
I, [2016-07-15T19:07:37.551064 #13]  INFO -- :
I, [2016-07-15T19:07:37.551586 #13]  INFO -- : > chown -R postgres:postgres /var/run/postgresql
I, [2016-07-15T19:07:37.553542 #13]  INFO -- :
I, [2016-07-15T19:07:37.554101 #13]  INFO -- : > /root/upgrade_postgres
initdb: invalid locale settings; check LANG and LC_* environment variables
I, [2016-07-15T19:07:37.618326 #13]  INFO -- : Upgrading PostgreSQL to from version 9.3 to 9.5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.


I, [2016-07-15T19:07:37.618859 #13]  INFO -- : > rm /root/upgrade_postgres
I, [2016-07-15T19:07:37.620569 #13]  INFO -- :
I, [2016-07-15T19:07:37.621197 #13]  INFO -- : Replacing data_directory = '/var/lib/postgresql/9.5/main' with data_directory = '/shared/postgres_data' in /etc/postgresql/9.5/main/postgresql.conf
I, [2016-07-15T19:07:37.622140 #13]  INFO -- : Replacing (?-mix:#?listen_addresses *=.*) with listen_addresses = '*' in /etc/postgresql/9.5/main/postgresql.conf
I, [2016-07-15T19:07:37.623527 #13]  INFO -- : Replacing (?-mix:#?synchronous_commit *=.*) with synchronous_commit = $db_synchronous_commit in /etc/postgresql/9.5/main/postgresql.conf
I, [2016-07-15T19:07:37.626119 #13]  INFO -- : Replacing (?-mix:#?shared_buffers *=.*) with shared_buffers = $db_shared_buffers in /etc/postgresql/9.5/main/postgresql.conf
I, [2016-07-15T19:07:37.628533 #13]  INFO -- : Replacing (?-mix:#?work_mem *=.*) with work_mem = $db_work_mem in /etc/postgresql/9.5/main/postgresql.conf
I, [2016-07-15T19:07:37.629774 #13]  INFO -- : Replacing (?-mix:#?default_text_search_config *=.*) with default_text_search_config = '$db_default_text_search_config' in /etc/postgresql/9.5/main/postgresql.conf
I, [2016-07-15T19:07:37.631441 #13]  INFO -- : > install -d -m 0755 -o postgres -g postgres /shared/postgres_backup
I, [2016-07-15T19:07:37.633837 #13]  INFO -- :
I, [2016-07-15T19:07:37.634384 #13]  INFO -- : Replacing (?-mix:#?max_wal_senders *=.*) with max_wal_senders = $db_max_wal_senders in /etc/postgresql/9.5/main/postgresql.conf
I, [2016-07-15T19:07:37.634999 #13]  INFO -- : Replacing (?-mix:#?wal_level *=.*) with wal_level = $db_wal_level in /etc/postgresql/9.5/main/postgresql.conf
I, [2016-07-15T19:07:37.638768 #13]  INFO -- : Replacing (?-mix:#?checkpoint_segments *=.*) with checkpoint_segments = $db_checkpoint_segments in /etc/postgresql/9.5/main/postgresql.conf
I, [2016-07-15T19:07:37.640357 #13]  INFO -- : Replacing (?-mix:#?logging_collector *=.*) with logging_collector = $db_logging_collector in /etc/postgresql/9.5/main/postgresql.conf
I, [2016-07-15T19:07:37.641837 #13]  INFO -- : Replacing (?-mix:#?log_min_duration_statement *=.*) with log_min_duration_statement = $db_log_min_duration_statement in /etc/postgresql/9.5/main/postgresql.conf
I, [2016-07-15T19:07:37.642999 #13]  INFO -- : Replacing (?-mix:^#local +replication +postgres +peer$) with local replication postgres  peer in /etc/postgresql/9.5/main/pg_hba.conf
I, [2016-07-15T19:07:37.644837 #13]  INFO -- : Replacing (?-mix:^host.*all.*all.*127.*$) with host all all 0.0.0.0/0 md5 in /etc/postgresql/9.5/main/pg_hba.conf
I, [2016-07-15T19:07:37.645949 #13]  INFO -- : > HOME=/var/lib/postgresql USER=postgres exec chpst -u postgres:postgres:ssl-cert -U postgres:postgres:ssl-cert /usr/lib/postgresql/9.5/bin/postmaster -D /etc/postgresql/9.5/main
I, [2016-07-15T19:07:37.647448 #13]  INFO -- : > sleep 5
LOG:  skipping missing configuration file "/shared/postgres_data/postgresql.auto.conf"
2016-07-15 19:07:37 UTC [48-2] FATAL:  database files are incompatible with server
2016-07-15 19:07:37 UTC [48-3] DETAIL:  The data directory was initialized by PostgreSQL version 9.3, which is not compatible with this version 9.5.3.
I, [2016-07-15T19:07:42.656023 #13]  INFO -- :
I, [2016-07-15T19:07:42.661508 #13]  INFO -- : > su postgres -c 'createdb discourse' || true
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = (unset),
    LC_ALL = (unset),
    LANG = "fr_FR.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
createdb: could not connect to database template1: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
I, [2016-07-15T19:07:42.783161 #13]  INFO -- :
I, [2016-07-15T19:07:42.783859 #13]  INFO -- : > su postgres -c 'psql discourse -c "create user discourse;"' || true
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = (unset),
    LC_ALL = (unset),
    LANG = "fr_FR.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
I, [2016-07-15T19:07:42.875928 #13]  INFO -- :
I, [2016-07-15T19:07:42.876859 #13]  INFO -- : > su postgres -c 'psql discourse -c "grant all privileges on database discourse to discourse;"' || true
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = (unset),
    LC_ALL = (unset),
    LANG = "fr_FR.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
I, [2016-07-15T19:07:42.952436 #13]  INFO -- :
I, [2016-07-15T19:07:42.952958 #13]  INFO -- : > su postgres -c 'psql discourse -c "alter schema public owner to discourse;"'
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = (unset),
    LC_ALL = (unset),
    LANG = "fr_FR.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
I, [2016-07-15T19:07:43.026779 #13]  INFO -- :
I, [2016-07-15T19:07:43.027764 #13]  INFO -- : Terminating async processes


FAILED
--------------------
Pups::ExecError: su postgres -c 'psql discourse -c "alter schema public owner to discourse;"' failed with return #<Process::Status: pid 74 exit 2>
Location of failure: /pups/lib/pups/exec_command.rb:108:in `spawn'
exec failed with the params "su postgres -c 'psql $db_name -c \"alter schema public owner to $db_user;\"'"
ea929c0df0e08313cb66ff8b957e042638cd04e2c03ffc62d29db5035138a40c
** FAILED TO BOOTSTRAP ** please scroll up and look for earlier error messages, there may be more than one
koan_dev@vps215774:/var/discourse$

Don’t understant the problem ? Is it going from psql 9.3 to 9.5 the issue?
And my discourse was updated frequently before.

Thanks for any help


Data directory "/shared/postgres_data" has group or world access
Connection issue
#2

For information, i’ve restored an image of my vps just after the failing upgrade, trying to find a solution to upgrade. (and sorry for my english, i’m french)


#3

Use

LANG: en_US.UTF-8

instead of

LANG: fr_FR.UTF-8

in your container configuration.

You can still set the French locale:

DISCOURSE_DEFAULT_LOCALE: fr

Database files are incompatible with server
#4

Man, you rock !

How did u find it ? Thanks you anyway !


#5

Got the same issue and saw hints leading to this particular setting.

Hope this topic will help others. Setting LANG to something different than en_US.UTF-8 never works it seems.


(Jill-Jênn Vie) #6

Got the same issue. It seems to be related to the following line:

LOG:  skipping missing configuration file "/shared/postgres_data/postgresql.auto.conf"

That appears under the fr_FR configuration, but not the en_EN configuration. Somehow, the database could be upgraded from PostgreSQL 9.3 to PostgreSQL 9.5. Ideas anyone?

N. B. – I did not upgrade Discourse within the last six months.