Done with commit Remove back to in displayed text
Must download and install PostgreSQL 10 into /usr/local. Following https://www.postgresql.org/docs/10/static/installation.html.
cd /usr/local/src/ git clone git://git.postgresql.org/git/postgresql.git cd postgresql git checkout REL_10_1 -b 10.1 mkdir build_dir cd build_dir ../configure make -j 8 sudo make install export LD_LIBRARY_PATH=/usr/local/pgsql/lib export PATH=/usr/local/pgsql/bin:$PATH psql --version > psql (PostgreSQL) 10.1
As I do not have much left space adding the last 100 GB of my SSD as /dev/sda6 -> /opt/data (ext4). Adding posgresql-10 via
sudo mkdir /opt/data/postgres-10.1 sudo ln -s /opt/data/postgres-10.1 /var/lib/postgresql/10.1
Reading https://www.postgresql.org/docs/10/static/upgrading.html. I will try the pg_upgrade, as this is faster see https://www.postgresql.org/docs/10/static/pgupgrade.html
Also I decided to rebuild postgres to create a new installation with better name, eg using sudo make prefix=/usr/local/pgsql-10.1 install
As we have trust as authentication in /etc/postgresql-8.4/pg_hba.conf (e.g. local all all trust)
The commands are
/usr/local/src/postgresql/build_dir sudo make prefix=/usr/local/pgsql-10.1 install sudo svc -d /service/* sudo -u postgres /usr/bin/pg_ctl -D /var/lib/postgresql/8.4/data/ stop sudo chown -R postgres:postgres /opt/data/postgres-10.1/ sudo -u postgres /usr/local/pgsql-10.1/bin/initdb --pgdata /opt/data/postgres-10.1/data cd /var/lib/postgresql/ sudo -u postgres /usr/local/pgsql-10.1/bin/pg_upgrade --old-bindir=/usr/bin --new-bindir=/usr/local/pgsql-10.1/bin --old-datadir=/var/lib/postgresql/8.4/data --new-datadir=/opt/data/postgres-10.1/data # /usr/local/pgsql-10.1/bin/pg_ctl -D /opt/data/postgres-10.1/ -l logfile start # sudo cp /etc/init.d/postgresql-8.4 /etc/init.d/postgresql-10.1 # sudo cp /etc/conf.d/postgresql-8.4 /etc/conf.d/postgresql-10.1 # Adjusting path in /etc/init.d/postgresql-10.1
Running the pg_upgrade command always leads to the error
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
connection to database failed: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.50432"?
could not connect to source postmaster started with the command:
"/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/8.4/data" -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000 -c listen_addresses='' -c unix_socket_permissions=0700" start
Failure, exiting
why? Tried running the commnad it has not problems
sudo -u postgres "/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/8.4/data" -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000 -c listen_addresses='' -c unix_socket_permissions=0700" start waiting for server to start.... done server started
After adding --old-options="-c unix_socket_permissions=0770 -D /etc/postgresql-8.4/" to the upgrade command I continue a little bit further, but I see in the pg_upgrade_server.log
command: "/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/8.4/data" -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000 -c unix_socket_permissions=0770 -D /etc/postgresql-8.4/ -c listen_addresses='' -c unix_socket_permissions=0700" start >> "pg_upgrade_server.log" 2>&1 waiting for server to start....FATAL: Datenverzeichnis »/etc/postgresql-8.4« hat falschen Eigentümer TIPP: Der Server muss von dem Benutzer gestartet werden, dem das Datenverzeichnis gehört.
running sudo chown -R postgres:postgres /etc/postgresql-8.4 and sudo chmod 0700 /etc/postgresql-8.4 Then I get @DETAIL: Die Datei »/etc/postgresql-8.4/PG_VERSION« fehlt. Fixing with sudo cp -p /var/lib/postgresql/8.4/data/PG_VERSION /etc/postgresql-8.4/
No. Correcting setup to allow access via unix socket
mkdir /var/lib/postgresql/8.4/socket chmod 0770 /var/lib/postgresql/8.4/socket sudo chown -R postgres:postgres /var/lib/postgresql/8.4/socket/ /usr/local/pgsql-10.1/bin/pg_upgrade --old-bindir=/usr/bin --new-bindir=/usr/local/pgsql-10.1/bin --old-datadir=/var/lib/postgresql/8.4/data/ --new-datadir=/opt/data/postgres-10.1/data --user=postgres --old-options="-c unix_socket_directory=/var/lib/postgresql/8.4/socket" sudo -u postgres mkdir /var/lib/postgresql/10.1/socket sudo -u postgres chmod 0770 /var/lib/postgresql/10.1/socket
This did not work. There I will try the alternate method creating a dump and loading it.
sudo -u postgres /usr/local/pgsql-10.1/bin/pg_dumpall --host localshot --file /opt/data/postgres-10.1/8.4.dumpall sudo ls -lh /opt/data/postgres-10.1/8.4.dumpall -rw-r--r-- 1 postgres postgres 13G Dec 11 12:09 /opt/data/postgres-10.1/8.4.dumpall
Adapting /var/lib/postgresql/10.1/data/postgresql.conf. (lc_ -> de_CH, english -> german)
Adapted also /etc/init.d/postgresql-10.1 (postgres > pg_ctl, setting PGDATA/DATA_DIR/PATH).
Running import manually
sudo -u postgres /usr/local/pgsql-10.1/bin/postgres -D /var/lib/postgresql/10.1/data sudo -u postgres /usr/local/pgsql-10.1/bin/psql -d postgres -f /opt/data/postgres-10.1/8.4.dumpall sudo -u postgres /usr/local/pgsql-10.1/bin/psql -d postgres -f /opt/data/postgres-10.1/8.4.dumpall
This took about 10 minutes, but produces some errors
2017-12-11 12:44:16.475 CET [31863] STATEMENT: CREATE ROLE postgres;
2017-12-11 12:45:26.163 CET [904] ERROR: could not access file "$libdir/tsearch2": Datei oder Verzeichnis nicht gefunden
2017-12-11 12:45:26.163 CET [904] STATEMENT: CREATE FUNCTION dex_init(internal) RETURNS internal
LANGUAGE c
AS '$libdir/tsearch2', 'tsa_dex_init';
2017-12-11 12:45:26.163 CET [904] ERROR: function public.dex_init(internal) does not exist
2017-12-11 12:45:26.163 CET [904] STATEMENT: ALTER FUNCTION public.dex_init(internal) OWNER TO postgres;
2017-12-11 12:45:26.163 CET [904] ERROR: could not access file "$libdir/tsearch2": Datei oder Verzeichnis nicht gefunden
2017-12-11 12:45:26.163 CET [904] STATEMENT: CREATE FUNCTION dex_lexize(internal, internal, integer) RETURNS internal
LANGUAGE c STRICT
AS '$libdir/tsearch2', 'tsa_dex_lexize';
<..>
2017-12-11 12:53:50.032 CET [11296] ERROR: could not open dictionary file "/usr/local/pgsql-10.1/share/tsearch_data/fulltext.dict": Datei oder Verzeichnis nicht gefunden
2017-12-11 12:53:50.032 CET [11296] STATEMENT: CREATE TEXT SEARCH DICTIONARY german_ispell (
TEMPLATE = pg_catalog.ispell,
dictfile = 'fulltext', afffile = 'fulltext', stopwords = 'fulltext' );
2017-12-11 12:53:50.032 CET [11296] ERROR: text search dictionary "german_ispell" does not exist
2017-12-11 12:53:50.032 CET [11296] STATEMENT: ALTER TEXT SEARCH DICTIONARY german_ispell OWNER TO apache;
2017-12-11 12:53:50.084 CET [11296] ERROR: text search dictionary "german_ispell" does not exist
2017-12-11 12:53:50.084 CET [11296] STATEMENT: ALTER TEXT SEARCH CONFIGURATION default_german
ADD MAPPING FOR word WITH german_ispell, german_stem;
2017-12-11 12:53:50.084 CET [11296] ERROR: text search dictionary "german_ispell" does not exist
2017-12-11 12:53:50.084 CET [11296] STATEMENT: ALTER TEXT SEARCH CONFIGURATION default_german
ADD MAPPING FOR numword WITH german_ispell, german_stem;
2017-12-11 12:53:50.150 CET [11296] ERROR: text search dictionary "german_ispell" does not exist
2017-12-11 12:53:50.150 CET [11296] STATEMENT: ALTER TEXT SEARCH CONFIGURATION default_german
ADD MAPPING FOR hword_numpart WITH german_ispell, german_stem;
2017-12-11 12:53:50.150 CET [11296] ERROR: text search dictionary "german_ispell" does not exist
2017-12-11 12:53:50.150 CET [11296] STATEMENT: ALTER TEXT SEARCH CONFIGURATION default_german
ADD MAPPING FOR hword_part WITH german_ispell, german_stem;
2017-12-11 12:53:50.203 CET [11296] ERROR: text search dictionary "german_ispell" does not exist
2017-12-11 12:53:50.203 CET [11296] STATEMENT: ALTER TEXT SEARCH CONFIGURATION default_german
ADD MAPPING FOR numhword WITH german_ispell, german_stem;
2017-12-11 12:53:50.217 CET [11296] ERROR: text search dictionary "german_ispell" does not exist
2017-12-11 12:53:50.217 CET [11296] STATEMENT: ALTER TEXT SEARCH CONFIGURATION default_german
ADD MAPPING FOR hword WITH german_ispell, german_stem;
Maybe we just can ignore these errors.
Restarting via /etc/init.d/postgresql-10.1 does not work at the moment. Ignoring this problem for the moment and testing migeld.
Must adapt config.ru of migel to use
export LD_LIBRARY_PATH=/usr/local/pgsql-10.1/lib export PATH=/usr/local/pgsql-10.1/bin:$PATH
Checking connection on command line works
$ psql -U migel --host localhost --port 5432 migel
psql (10.1)
Type "help" for help.
migel=> \dt
List of relations
Schema | Name | Type | Owner
--------+-------------------------------------+-------+-------
public | collection | table | migel
public | migel_fulltext_index_de | table | migel
public | migel_fulltext_index_fr | table | migel
<...>
migel=> select count(*) from migel_fulltext_index_de;
count
-------
581
(1 row)
But we have problems using pg_db
sudo -u apache bundle-240 exec ruby-240 bin/migeld
/usr/local/pgsql-10.1/bin:/var/www/migel/vendor/ruby/2.4.0/bin:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/opt/bin
/usr/local/pgsql-10.1/lib
The PGconn, PGresult, and PGError constants are deprecated, and will be
removed as of version 1.0.
You should use PG::Connection, PG::Result, and PG::Error instead, respectively.
Called from /var/www/migel/vendor/ruby/2.4.0/gems/ydbd-pg-0.5.3/lib/dbd/Pg.rb:157:in `new'
/var/www/migel/vendor/ruby/2.4.0/gems/ydbd-pg-0.5.3/lib/dbd/pg/database.rb:82:in `rescue in initialize': could not connect to server: No such file or directory (DBI::OperationalError)
Is the server running locally and accepting
connections on Unix domain socket "/run/postgresql/.s.PGSQL.5432"?
from /var/www/migel/vendor/ruby/2.4.0/gems/ydbd-pg-0.5.3/lib/dbd/pg/database.rb:41:in `initialize'
from /var/www/migel/vendor/ruby/2.4.0/gems/ydbd-pg-0.5.3/lib/dbd/Pg.rb:157:in `new'
from /var/www/migel/vendor/ruby/2.4.0/gems/ydbd-pg-0.5.3/lib/dbd/Pg.rb:157:in `connect'
from /var/www/migel/vendor/ruby/2.4.0/gems/ydbi-0.5.3/lib/dbi/handles/driver.rb:33:in `connect'
from /var/www/migel/vendor/ruby/2.4.0/gems/ydbi-0.5.3/lib/dbi.rb:148:in `connect'
If I force in ydbd-pg-0.5.3/lib/dbd/pg/database.rb to use host localhost, I proceed a little further and get the error /var/www/migel/vendor/ruby/2.4.0/gems/ydbd-pg-0.5.3/lib/dbd/pg/database.rb:394:in `block in load_type_map': undefined method `[]' for DBI::Type::Varchar:Class (NoMethodError)
Looks like the gem dbd-pg, pg, ydbd-pg do not run without modification with postgreSQL 10. Using pry to analyse the situation a bit more deeply. An alternative solution would be to drop no longer maintained dbd/pb with sequel, which would require a significant rewrite of odba. But maybe we could use its class Sequel::DBI.
Found a simple patch for vendor/ruby/2.4.0/gems/ydbd-pg-0.5.3/lib/dbd/pg/database.rb. Adding unless key.is_a(Integer) at line made 402 made migeld and oddb.org start.
Pushed commit Fix running under postgresql 10
Running watir tests.