Description of problem: $ postgresql-setup upgrade The above should complete no problems. When I try this with Fedora 24, it complains that the data is in the new format already. If I try to start the database without it, it complains it is in the old format. I am sorry I don't have the exact messages. The system is a semi-production system used to develop and do long term testing and I had to restore from backup. Version-Release number of selected component (if applicable): 9.4.6 and 9.5.1 How reproducible: Tested twice Steps to Reproduce: 1. Install fedora 23 2. Create a database with data 3. fedup system upgrade to 24 4. postgresql-setup upgrade (or manual equivalent) 5. See failure.
Worked for me on f23 -> rawhide. As you've done fedup, can you post output from: # rpm -q postgresql-server postgresql-upgrade postgresql postgresql-server-9.5.1-2.fc25.x86_64 postgresql-upgrade-9.5.1-2.fc25.x86_64 postgresql-9.5.1-2.fc25.x86_64 Don't you have some weird combination of F23/F24 packages?
Well, yes - I'll try the fedup way too.
Seems to work for me, I need a reproducer: # dnf update -y # cat /etc/os-release | grep PRETTY PRETTY_NAME="Fedora 23 (Cloud Edition)" # dnf install -y postgresql-server pagila fedup # postgresql-setup --initdb # service postgresql start # su - postgres $ cat /usr/share/pagila/pagila-schema.sql /usr/share/pagila/pagila-data.sql | psql $ exit # fedup --network 24 ... Install 21 Packages Upgrade 275 Packages Downgrade 11 Packages Total download size: 211 M ... (the Downgrade part is due to missing updates repos? ... ... anyway, I was surprised that the pkg downgrade was actually allowed ... # dnf system-upgrade reboot # cat /etc/os-release | grep PRETTY PRETTY_NAME="Fedora 24 (Cloud Edition)" # service postgresql start Job for postgresql.service failed because the control process ... # service postgresql status .. An old version of the database format was found. .. Use 'postgresql-setup --upgrade' to upgrade to version '9.5' # dnf install postgresql-upgrade # postgresql-setup upgrade WARNING: using obsoleted argument syntax, try --help WARNING: arguments transformed to: postgresql-setup --upgrade --unit postgresql * Upgrading database. * Upgraded OK. WARNING: The configuration files were replaced by default configuration. WARNING: The previous configuration and data are stored in folder WARNING: /var/lib/pgsql/data-old. * See /var/lib/pgsql/upgrade_postgresql.log for details. # service postgresql start # su - postgres $ psql psql (9.5.1) Type "help" for help. postgres=# \d+ List of relations Schema | Name | Type | Owner | Size | Description --------+----------------------------+----------+----------+------------+------------- public | actor | table | postgres | 40 kB | public | actor_actor_id_seq | sequence | postgres | 8192 bytes | public | actor_info | view | postgres | 0 bytes | public | address | table | postgres | 88 kB | public | address_address_id_seq | sequence | postgres | 8192 bytes | public | category | table | postgres | 8192 bytes |
Sorry, it was dnf system-upgrade not fedup. [root@Taipei ~]# dnf list extras Waiting for process with pid 1920 to finish. Last metadata expiration check performed 0:00:05 ago on Thu Mar 17 13:05:23 2016. Extra Packages kernel.x86_64 4.4.4-301.fc23 @System kernel.x86_64 4.4.5-300.fc23 @@commandline kernel-core.x86_64 4.4.4-301.fc23 @System kernel-core.x86_64 4.4.5-300.fc23 @@commandline kernel-modules.x86_64 4.4.4-301.fc23 @System kernel-modules.x86_64 4.4.5-300.fc23 @@commandline [root@Taipei ~]# postgresql-setup upgrade WARNING: using obsoleted argument syntax, try --help WARNING: arguments transformed to: postgresql-setup --upgrade --unit postgresql * Upgrading database. ERROR: pg_upgrade tool failed ERROR: Upgrade failed. * See /var/lib/pgsql/upgrade_postgresql.log for details. command: "/usr/lib64/pgsql/postgresql-9.4/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/data-old" -o "-p 5432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/pgsql'" start >> "pg_upgrade_server.log" 2>&1 waiting for server to start....FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 9.5, which is not compatible with this version 9.4.6. stopped waiting pg_ctl: could not start server Examine the log output. postgresql-9.5.1-2.fc24.x86_64 postgresql-devel-9.5.1-2.fc24.x86_64 postgresql-libs-9.5.1-2.fc24.x86_64 postgresql-server-9.5.1-2.fc24.x86_64 postgresql-upgrade-9.5.1-2.fc24.x86_64 I have made sure I have the English langpack installed since en_US.UTF-8 is the full locale data. I have made sure that all connections are trusted, so that isn't it.
Does postgres mess with the data if it is an old version before it knows it is? The system in question has systemd start postgres.
No, that isn't it. Upon doing a dnf system-upgrade back to 23, instead of 24, and doing dnf reinstall '*' postgres started just fine with all of the data intact. So, I am not sure why you are seeing it work and I am not.
Would I have to do a dnf reinstall '*' for f24 to work correctly after the upgrade? I figured I had to do that when downgrading just because of all the weird things a downgrade does.
No, the dnf reinstall was NOT needed today when downgrading. It was last go round. However, I do have archive mode on and the appropriate wal_level for archiving (not clustering). I do not know if that makes a difference for the upgrades. I am not sure how to make the needed changes if that is the problem.
This snippet is important: (In reply to Trever Adams from comment #4) > waiting for server to start....FATAL: database files are incompatible with > server > > DETAIL: The data directory was initialized by PostgreSQL version 9.5, which > is not compatible with this version 9.4.6. How does it happen that your data directory has PG_VERSION set to 9.5? Could you check: $ cat data/PG_VERSION 9.4 .. _before_ you run the 'postgresql-setup --upgrade' command?
Btw. seems like fedup is just thin wrapper around 'dnf system-upgrade',
I am sorry, I thought I responded to this. For some reason, it didn't take. data]# cat PG_VERSION 9.4 Do I have to do anything before running 'postgresql-setup --upgrade'? Do I have to run it as postgres?
Something is wrong here. The message 'The data directory was initialized by PostgreSQL version 9.5' comes from 'src/backend/utils/init/miscinit.c'. It is clear that the '9.5' part of string is result of parsing 'data/PG_VERSION'. And I don't think postgresql-setup should touch 'data/PG_VERSION'. Can you detect who is responsible for changing the contents of 'data/PG_VERSION'? Or is it possible that we talk about two data directories?
To the best of my knowledge nothing ever changed the PG_VERSION file. I checked that before I filed the bug report. I couldn't figure it out. It is the same all the way through. And no, only one data directory.
I am afraid I just had to go ahead with pg_dumpall/psql. I do not know why things didn't work. I only have two other machines that will likely have this happen on. Unfortunately, the data there is not critical but the functions of the machines are, so when the time comes it will either work immediately or I will just be forced to dump the data and move on.
Hi Trever, > To the best of my knowledge nothing ever changed the PG_VERSION file. the thing is (if there is no middle step before 'postgresql-setup --upgrade') that the PG_VERSION file contains version 9.5 --> so if you were able to run the cluster in F23 against that file, it sounds really unlikely that you used to run the postgresql 9.4 version provided by "official" Fedora repositories. Can it be possible that you installed self-compiled postgresql server, or you installed third-party packages? Reading the bug once more, you still haven't provided: - what packages you had on F23 - log showing that you are able to start the 9.4.X version on F23 (In reply to Trever Adams from comment #14) > I am afraid I just had to go ahead with pg_dumpall/psql. I do not know why > things didn't work. I only have two other machines that will likely have > this happen on. Unfortunately, the data there is not critical but the > functions of the machines are, so when the time comes it will either work > immediately or I will just be forced to dump the data and move on. I would be glad to see what is going on here and possibly fix the issue. Can you please update the thread if you figure out the issue? In the meantime, I'll probably close as INSUFFICIENT_DATA within a week or so.
Please feel free to reopen with any additional info.
This appears to have been fixed in 9.5.2-1. The only problem I have on the remaining system is apparently due to a schema problem (something being required NOT NULL but not being declared as such).
DETAIL: The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 9.5.7. This is back with Fedora 26 on the same machine. I do have wal_level = archive set for this machine. I do not know why this keeps rearing its ugly head. PG_VERSION has 9.5. I am not changing it manually. I am not using modified scripts. The one machine where I have not set wal_level everything works as it should. The last time around the other two machines where I use wal_level do not have important data (just nice to keep) and I simply let the data die.
The three machines use postgresql primary for the following: DSPAM DSPAM Zabbix. The PG_VERSION is 9.5 prior to upgrade and after the failed attempt. Comment #17 seems to be erroneous and should be ignored.
Sorry, but I'm still unable to reproduce this issue. Upgrade just works for me for 9.5 -> 9.6 case; please attach all logs from /var/lib/pgsql.
Created attachment 1287355 [details] Log from /var/lob/pgsql
Created attachment 1287356 [details] internal upgrade log
Created attachment 1287357 [details] upgrade server log
Created attachment 1287358 [details] upgrade utility log
$ /usr/bin/pg_upgrade -d /db-up/pgsql/data -D /var/lib/pgsql/data -b /usr/lib64/pgsql/postgresql-9.5/bin -B /usr/bin -U postgres is the command being run. I have checked the for selinux errors, just in case, all messages say success. The system that did not have wal_level = archive is an Odoo setup used for testing.
Would where the archive logs are matter? Mine were in /var/lib/pgsql-archive. I cannot remember what the problem was that necessitated this, but that is where they were instead of /var/lib/pgsql/backup.
(In reply to Trever Adams from comment #25) > $ /usr/bin/pg_upgrade -d /db-up/pgsql/data -D /var/lib/pgsql/data -b > /usr/lib64/pgsql/postgresql-9.5/bin -B /usr/bin -U postgres Uh, this is weird ... You initially talked about 'postgresql-setup upgrade' command (comment #0). So this is probably completely different bug, right? I would suspect that you misuse '-d' and '-D' if pg_upgrade claims the PG_VERSION is wrong.
No, as I remember postgresql-setup upgrade just wraps the pg_upgrade command. When I first was trying to track this bug, the postgresql-setup didn't have adequate checking and it ate one of my databases. So, I created my own procedure and have used it since. (All copy and paste at the right points, with only one pace being modified, which is the version in the -b command.) -d and -D are correct.
If -d and -D were wrong, it wouldn't have worked (since it is a procedure) on the Odoo 9c database on another machine (no wal_level = archive) just a few minutes prior.
> No, as I remember postgresql-setup upgrade just wraps the pg_upgrade > command. That's not that easy -- it should wrap it the correct way... It takes care of all the permission problems, it runs initdb appropriately, takes care of versioning, etc. > When I first was trying to track this bug, the postgresql-setup didn't > have adequate checking and it ate one of my databases. First, please always backup appropriately. Second, it is absolutely important to report issues like this, if those ever happened to you (I don't think there's place for such bugs in postgresql-setup, but still I have to tell this just in case). --- Trever, we don't have enough data to reproduce this issue. To move forward, please understand that we need either (a) step by step reproducer (including all the 'initdb', 'cp', 'mv', 'chcon', commands you've done before running 'pg_upgrade') or just (b) try again with 'postgresql-setup --upgrade'. The (b) is definitely preferred, as you are unlikely to make a mistake on the way. Sorry, feel free to reopen or comment further and thanks for patience.
First off, I did try on one of the upgrades doing postgresql-setup --upgrade. The logs are identical other than dates/times and files/directories. Same failure with the version code being incorrect. A pg_dumpall and psql -f thefile postgres works. (I did the dumpall before the fedup the reload after.) Ok, create either a zabbix or DSPAM setup using Postgresql. Make sure wal_level = archive. Make sure the archive directory is /var/lib/pgsql-archive not /var/lib/pgsql/backups. Use it for a while (a day or two may be necessary, I don't know). UPGRADE OLD POSTGRESQL DATABASE dnf install postgresql-upgrade.x86_64 cd / mkdir /db-temp mkdir /db-up chown postgres:postgres /db-up chmod 777 /db-temp cd /db-up cp -a /var/lib/pgsql . cd /db-temp rm -rfv /var/lib/pgsql/data/ postgresql-setup --initdb --unit postgresql su postgres /usr/bin/pg_upgrade -d /db-up/pgsql/data -D /var/lib/pgsql/data -b /usr/lib64/pgsql/postgresql-OLD_VER/bin -B /usr/bin -U postgres exit service postgresql restart su postgres ./analyze_new_cluster.sh IF ALL WORKS: su postgres ./delete_old_cluster.sh exit rm -rfv /db-up /db-temp You will notice that the only part that needs changing is OLD_VER. It works on any setup I have had where wal_level is none. I believe you will see that permissions were not a problem. Again, it works on several systems where wal_level is left at the default. I did report the bug somewhere with postgresql-setup --upgrade eating the database. The problem was the disk was a little short on space compared to what was needed and the script doesn't do checking (or didn't) and erased one copy without the other being valid. I had backups from a few hours earlier and the nature of the system was such that that was acceptable.
checkpoint_segments 10
To clarify a bit, wal_level = archive, archive_mode = on, archive_command = test ! -f /var/lib/pgsql-archive/%f && cp %p /var/lib/pgsql-archive/%f. The directory /var/lib/pgsql-archive/ has owner => 'postgres', group => 'disk', mode => '0770'.
Re-opening to give it a try with reproducing. But note that > Ok, create either a zabbix or DSPAM setup using Postgresql. [snip] > Use it for a while (a day or two may be necessary, I don't know). .. this might complicate things a bit. Some more clear and deterministic reproducer would be appreciated. I still don't see how it happens that '9.6' string got into PG_VERSION in /db-up/pgsql/data. No command in your reproducer should be responsible for this; this looks like some user error tbh.
Hello. It seems I hit the same issue when upgrading from 9.5 to 9.6. Executed command: /usr/bin/pg_upgrade -b /usr/lib64/pgsql/postgresql-9.5/bin/ -B /usr/bin/ -d /data/citus/data_9.5 -D /data/citus/data --check Result from pg_upgrade_server.log: ----------------------------------------------------------------- pg_upgrade run on Thu Aug 10 12:49:15 2017 ----------------------------------------------------------------- command: "/usr/lib64/pgsql/postgresql-9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/data/citus/data_9.5" -o "-p 50432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/data/citus'" start >> "pg_upgrade_server.log" 2>&1 waiting for server to start....28415-- FATAL: database files are incompatible with server 28415-- DETAIL: The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 9.5.7. stopped waiting pg_ctl: could not start server Examine the log output. PG_VERSION files look ok: $ cat /data/citus/data_9.5/PG_VERSION 9.5 $ cat /data/citus/data/PG_VERSION 9.6 Currently I cannot afford dump+restore procedure due to the lack of disk space, so it would be nice to get to the bottom of the issue.
It seems it also doesn't work when trying to start postgres using binaries from /usr/lib64/pgsql/postgresql-9.5/bin directory: 25201-- DETAIL: The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 9.5.7. So it seems the bug is somewhere in the 9.5 version of the binaries installed with 9.6.
That version in error message comes from PG_VERSION file. So if it claims the data is initialized by `9.6` version, the PG_VERSION has to contain this string (which is contrary to what you claimed in comment #36). We really need a minimal reproducer, please try to provide one.
Steps to reproduce: 1. Upgrade postgresql from 9.5.3-2 to 9.6.3-6 2. As dedicated 'postgres' user, rename original data directory to data_9.5: $ mv ~/data ~/data_9.5 3. As root user, initialize new database in original data directory: # postgresql-setup initdb WARNING: using obsoleted argument syntax, try --help WARNING: arguments transformed to: postgresql-setup --initdb --unit postgresql * Initializing database in '/data/citus/data' * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log 4. Confirm version in PG_VERSION in both new and old data directories $ cat ~/data/PG_VERSION ~/data_9.5/PG_VERSION 9.6 9.5 5. As dedicated 'postgres' user, try to perform upgrade check: $ /usr/bin/pg_upgrade -b /usr/lib64/pgsql/postgresql-9.5/bin/ -B /usr/bin/ -d ~/data_9.5 -D ~/data --check Performing Consistency Checks ----------------------------- Checking cluster versions ok *failure* Consult the last few lines of "pg_upgrade_server.log" for the probable cause of the failure. 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 "/data/citus/.s.PGSQL.50432"? could not connect to old postmaster started with the command: "/usr/lib64/pgsql/postgresql-9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/data/citus/data_9.5" -o "-p 50432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/data/citus'" start Failure, exiting 6. Check logs: $ cat ~/pg_upgrade_server.log ----------------------------------------------------------------- pg_upgrade run on Tue Aug 29 08:33:12 2017 ----------------------------------------------------------------- command: "/usr/lib64/pgsql/postgresql-9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/data/citus/data_9.5" -o "-p 50432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/data/citus'" start >> "pg_upgrade_server.log" 2>&1 waiting for server to start....5306-- FATAL: database files are incompatible with server 5306-- DETAIL: The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 9.5.7. stopped waiting pg_ctl: could not start server Examine the log output. 7. Try to manually start postgresql-9.5 that comes with postgresql-9.6.3-6 package: $ /usr/lib64/pgsql/postgresql-9.5/bin/postgres -D ~/data_9.5 5507-- FATAL: database files are incompatible with server 5507-- DETAIL: The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 9.5.7. 8. Confirm PG_VERSION string again: $ cat ~/data_9.5/PG_VERSION 9.5 Hopefully that's enough.
OK, forget it. I have just realized that I explicitly set 'data_directory' to the '/data/citus/data' and that's why it gets version string from wrong PG_VERSION file.
Argh, thanks for reporting back! Trever, aren't you too using the 'data_directory' option?
data_directory = '/var/lib/pgsql/data' which I believe is the Fedora default. So, yes. But this wouldn't explain why some systems (same configuration enforced by puppet) will upgrade and some don't. I think it might be good to fix Postgresql to compare this value to the default and act appropriately. The puppet module I use is from puppetlabs. It forces that to be set (https://github.com/puppetlabs/puppetlabs-postgresql). I have filed the puppetlabs bug here: https://tickets.puppetlabs.com/browse/MODULES-5556?jql=project%20%3D%20MODULES%20AND%20resolution%20%3D%20Unresolved%20AND%20priority%20%3D%20Major%20ORDER%20BY%20key%20DESC If any of you have accounts/relationships with the postgresql project, can you look at getting paragraph 2, sentence 1 looked at? Of course, this still doesn't explain why some systems upgrade and some don't with the option set.
In fedora/upstream default is to not set `data_directory` option, and keep the data in the same directory as the configuration files. Turns out 'pg_upgrade' tool dislikes the 'data_directory' option in 'postgresql.conf'. Per documentation [1], If you wish to keep the configuration files elsewhere than the data directory, the postgres -D command-line option or PGDATA environment variable must point to the directory containing the configuration files, and the data_directory parameter must be set in postgresql.conf ..skip.. Notice that data_directory overrides -D and PGDATA for the location of the data directory ..skip.. But 'pg_upgrade' starts the servers with 'pg_ctl -D <datadir> start'. I guess this needs some upstream change, be that documentation in `pg_upgrade` or maybe try to use 'pg_ctl -D <confdir> -o "-c data_directory=<datadir>"'. As automatized solution is not trivial and will take some time, work-around could be: $ pg_upgrade -b oldbindir -B newbindir \ -d olddatadir -D newdatadir \ -o "-c data_directory='olddatadir' \ -O "-c data_directory='newdatadir' [1] https://www.postgresql.org/docs/9.6/static/runtime-config-file-locations.html
This message is a reminder that Fedora 26 is nearing its end of life. Approximately 4 (four) weeks from now Fedora will stop maintaining and issuing updates for Fedora 26. It is Fedora's policy to close all bug reports from releases that are no longer maintained. At that time this bug will be closed as EOL if it remains open with a Fedora 'version' of '26'. Package Maintainer: If you wish for this bug to remain open because you plan to fix it in a currently maintained version, simply change the 'version' to a later Fedora version. Thank you for reporting this issue and we are sorry that we were not able to fix it before Fedora 26 is end of life. If you would still like to see this bug fixed and are able to reproduce it against a later version of Fedora, you are encouraged change the 'version' to a later Fedora version prior this bug is closed as described in the policy above. Although we aim to fix as many bugs as possible during every release's lifetime, sometimes those efforts are overtaken by events. Often a more recent Fedora release includes newer upstream software that fixes bugs or makes them obsolete.
Fedora 26 changed to end-of-life (EOL) status on 2018-05-29. Fedora 26 is no longer maintained, which means that it will not receive any further security or bug fix updates. As a result we are closing this bug. If you can reproduce this bug against a currently maintained version of Fedora please feel free to reopen this bug against that version. If you are unable to reopen this bug, please file a new report against the current release. If you experience problems, please add a comment to this bug. Thank you for reporting this bug and we are sorry it could not be fixed.
postgresql-10.4-1.fc28.x86_64 (at least 10.3 in F28 at the time tried) still has this problem.