Package: postgresql-9.1 Version: 9.1.1-1~bpo60+1 Hi,
To use pg_upgrade, first one has to install postgresql-server-dev-9.1 so /usr/lib/postgresql/9.1/bin/pg_config is found. The postgresql-9.1 package should at least have that in the Suggests: relationship then, and/or mention it in a README. In fact, shipping a pg_upgrade manual page would be most clear. And then, by the book at http://www.postgresql.org/docs/current/static/pgupgrade.html I do: % sudo /etc/init.d/postgresql stop Stopping PostgreSQL 8.3 database server: main. Stopping PostgreSQL 9.1 database server: main. % time sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_upgrade -d /var/lib/postgresql/8.3/main -D /var/lib/postgresql/9.1/main -b /usr/lib/postgresql/8.3/bin -B /usr/lib/postgresql/9.1/bin -k -v Running in verbose mode Performing Consistency Checks ----------------------------- Checking current, bin, and data directories ok Checking cluster versions ok "/usr/lib/postgresql/8.3/bin/pg_ctl" -w -l "/dev/null" -D "/var/lib/postgresql/8.3/main" -o "-p 5432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000" start >> "/dev/null" 2>&1 Checking database user is a superuser ok Checking for prepared transactions ok Checking for reg* system oid user data types ok Checking for contrib/isn with bigint-passing mismatch ok Checking for invalid 'name' user columns ok Checking for tsquery user columns ok Checking for contrib/ltree ok Creating script to adjust sequences ok Checking for large objects ok Creating catalog dump "/usr/lib/postgresql/9.1/bin/pg_dumpall" --port 5432 --username "postgres" --schema-only --binary-upgrade > "/root/pg_upgrade/9.1/pg_upgrade_dump_all.sql" ok "/usr/lib/postgresql/8.3/bin/pg_ctl" -w -l "/dev/null" -D "/var/lib/postgresql/8.3/main" stop >> "/dev/null" 2>&1 "/usr/lib/postgresql/9.1/bin/pg_ctl" -w -l "/dev/null" -D "/var/lib/postgresql/9.1/main" -o "-p 5432 -b" start >> "/dev/null" 2>&1 There were problems executing "/usr/lib/postgresql/9.1/bin/pg_ctl" -w -l "/dev/null" -D "/var/lib/postgresql/9.1/main" -o "-p 5432 -b" start >> "/dev/null" 2>&1 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 "/var/run/postgresql/.s.PGSQL.5432"? unable to connect to new postmaster started with the command: "/usr/lib/postgresql/9.1/bin/pg_ctl" -w -l "/dev/null" -D "/var/lib/postgresql/9.1/main" -o "-p 5432 -b" start >> "/dev/null" 2>&1 Failure, exiting sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_upgrade -d -D -b -B -k -v 4,05s user 3,26s system 7% cpu 1:36,55 total Now, why anyone would ever try to run the PostgreSQL server with -l /dev/null as well as >/dev/null 2>&1, that's completely beyond me. Once I try it manually with the proper -l setting, I see: postgres cannot access the server configuration file "/var/lib/postgresql/9.1/main/postgresql.conf": No such file or directory Hrm. So I symlink that analogous to how it is in 8.3: % sudo ln -s /etc/postgresql/9.1/main/postgresql.conf /var/lib/postgresql/9.1/main/ % sudo -u postgres "/usr/lib/postgresql/9.1/bin/pg_ctl" -w -l "/dev/null" -D "/var/lib/postgresql/9.1/main" -o "-p 5432 -b" start waiting for server to start....... done server started % echo $? 0 But then pg_upgrade requires me to stop the server again and restarts the whole sequence of checks and dumps again :) Eventually though it starts performing the upgrade and succeeds. However, it doesn't leave a postmaster running, and doesn't integrate with the init script, so: % sudo /etc/init.d/postgresql start Starting PostgreSQL 8.3 database server: mainThe PostgreSQL server failed to start. Please check the log output: postgres: could not find the database system Expected to find it in the directory "/var/lib/postgresql/8.3/main", but could not open file "/var/lib/postgresql/8.3/main/global/pg_control": No such file or directory ... failed! failed! Looks like get_versions() needs to check for these things, right now it just checks for: [ -x /usr/lib/postgresql/$v/bin/pg_ctl ] && [ ! -x /etc/init.d/postgresql-$v ] So as a workaround I do: % sudo ln -s /bin/false /etc/init.d/postgresql-8.3 And then it starts, but again at port 5433 as it was installed by default - pg_upgrade didn't update that like pg_upgradecluster would have. (I'm guessing it also didn't do all that config transition stuff pg_upgradecluster would do.) After a manual change of that, there's yet still another problem: % sudo -u postgres psql Welcome to psql 8.3.14 (server 9.1.1), the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit WARNING: You are connected to a server with major version 9.1, but your psql client is major version 8.3. Some backslash commands, such as \d, might not work properly. postgres=# This is because /usr/share/postgresql-common/PgCommon.pm's user_cluster_map() function looks for $defaultport (5432), so you also have to explicitly change the old cluster configuration to use a different port to avoid it getting in the way. So a few relatively minor changes, basically integrating pg_upgrade and pg_upgradecluster functionality, would be required to get this to run smoothly. Please fix it. TIA. -- 2. That which causes joy or happiness. -- To UNSUBSCRIBE, email to [email protected] with a subject of "unsubscribe". Trouble? Contact [email protected]
