# Postgres Encoding error during database restore **URL:** \ **Category:** Rocky Linux Help & Support\ **Created:** [November 3, 2021, 10:12pm UTC](https://forums.rockylinux.org/t/postgres-encoding-error-during-database-restore/4460 "2021-11-03T22:12:54Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![whichiso](https://avatars.discourse-cdn.com/v4/letter/w/838e76/32.png) [@whichiso](https://forums.rockylinux.org/u/whichiso)\ **Post date:** [November 3, 2021, 10:12pm UTC](https://forums.rockylinux.org/t/postgres-encoding-error-during-database-restore/4460/1 "2021-11-03T22:12:54Z")
I realize this is more so Postgresql and not a Rocky linux problem, but perhaps someone knows. 4+ hours of googling hasn’t solved it. I need to migrate my databases from the centOS7 with postgres9 to Rocky8.4 with postgres10. In the past, as the postgres user, I initialized the database with `initdb --locale=C -D /var/lib/pgsql/data` using locale=C to overcome UTF8 errors when restoring the database backup. To initialize the postgres10 database, Rocky8 tutorials recommended using, `postgresql-setup --initdb --unit postgresql` which does not allow/recognise --locale=C. With out using --locale=C, when I import my databases I get the encoding errors. > psql -U postgres -f pg\_dbs.bkp postgres > > ERROR: encoding “LATIN1” does not match locale “en\_US.UTF-8” > DETAIL: The chosen LC\_CTYPE setting requires encoding “UTF8”. Is there an alternate way to set --locale=C using the `postgresql-setup --initdb --unit postgresql` command? When I backup my databases using pg\_dumpall (`pg_dumpall -c -U postgres > pg_dbs.bkp`), is there a way to strip the encoding or change the encoding to UTF8? ---
**Author:** ![JustSomeone](https://avatars.discourse-cdn.com/v4/letter/j/7ea924/32.png) [@JustSomeone](https://forums.rockylinux.org/u/JustSomeone)\ **Post date:** [November 4, 2021, 11:14am UTC](https://forums.rockylinux.org/t/postgres-encoding-error-during-database-restore/4460/2 "2021-11-04T11:14:47Z")
Hello @whichiso i have no experiance with postgres database but if it was mysql i would do the following 1. create a vm with centos 7 and postgres 9 2. copy the data oon that vm and restore it 3. upgrade the database to version 10 4. do some testing to confirm it working fine 5. create backup of the new data 6. create new vm with rocky 8.4 then install postgres 10 7. restore that data to the rocky 8. confirm everything working fine then you ready this will make you sure that you look for single issue at time so if there issue converting from version 9 to 10 then that where you search for also the database upgrade process fix most of the issue when they change some of the encoding or maybe the file format hope that help and have a nice day 🙂 ---
**Author:** ![whichiso](https://avatars.discourse-cdn.com/v4/letter/w/838e76/32.png) [@whichiso](https://forums.rockylinux.org/u/whichiso)\ **Post date:** [November 4, 2021, 10:47pm UTC](https://forums.rockylinux.org/t/postgres-encoding-error-during-database-restore/4460/3 "2021-11-04T22:47:15Z")
@JustSomeone mysql (mariadb) is my next project. thanks. ---
**Author:** ![JustSomeone](https://avatars.discourse-cdn.com/v4/letter/j/7ea924/32.png) [@JustSomeone](https://forums.rockylinux.org/u/JustSomeone)\ **Post date:** [November 5, 2021, 8:14am UTC](https://forums.rockylinux.org/t/postgres-encoding-error-during-database-restore/4460/4 "2021-11-05T08:14:15Z")
you welcome and nice ---
**Author:** ![iwalker](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.rockylinux.org/iwalker/32/2599_2.png) [@iwalker](https://forums.rockylinux.org/u/iwalker)\ **Post date:** [November 5, 2021, 11:21am UTC](https://forums.rockylinux.org/t/postgres-encoding-error-during-database-restore/4460/5 "2021-11-05T11:21:46Z")
@whichiso I upgraded mine from postgres 9.2 to 10 like this: on RL8 system do: ```auto dnf install postgresql-server postgresql-upgrade ``` Copy the /var/lib/pgsql/data directory from your CentOS7 or RHEL7 system to /var/lib/pgsql/data directory on RL8 system. Since by default it wanted to do en\_US-UTF-8 on my system, but I needed en\_GB-UTF-8, I needed to pass the parameters like this: ```auto [root@rocky ~]# PGSETUP_INITDB_OPTIONS="--locale=en_GB.UTF-8" 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. ``` the results from the upgrade process: ```auto [root@rocky pgsql]# cat upgrade_postgresql.log Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for system-defined composite types in user tables ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Checking for invalid "unknown" user columns ok Checking for roles starting with "pg_" ok Checking for incompatible "line" data type ok Creating dump of global objects ok Creating dump of database schemas joomla postgres template1 ok Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok Checking for new cluster tablespace directories ok If pg_upgrade fails after this point, you must re-initdb the new cluster before continuing. Performing Upgrade ------------------ Analyzing all rows in the new cluster ok Freezing all rows in the new cluster ok Deleting files from new pg_xact ok Copying old pg_clog to new server ok Setting next transaction ID and epoch for new cluster ok Deleting files from new pg_multixact/offsets ok Setting oldest multixact ID in new cluster ok Resetting WAL archives ok Setting frozenxid and minmxid counters in new cluster ok Restoring global objects in the new cluster ok Restoring database schemas in the new cluster joomla postgres template1 ok Setting minmxid counter in new cluster ok Adding ".old" suffix to old global/pg_control ok If you want to start the old cluster, you will need to remove the ".old" suffix from /var/lib/pgsql/data-old/global/pg_control.old. Because "link" mode was used, the old cluster cannot be safely started once the new cluster has been started. Linking user relation files /var/lib/pgsql/data-old/base/16384/12883 /var/lib/pgsql/data-old/base/16384/12885 /var/lib/pgsql/data-old/base/16384/12749 /var/lib/pgsql/data-old/base/16384/12751 /var/lib/pgsql/data-old/base/16384/16388 /var/lib/pgsql/data-old/base/16384/16388_fsm /var/lib/pgsql/data-old/base/16384/16388_vm /var/lib/pgsql/data-old/base/16384/16395 /var/lib/pgsql/data-old/base/16384/16397 /var/lib/pgsql/data-old/base/16384/16398 /var/lib/pgsql/data-old/base/16384/16400 /var/lib/pgsql/data-old/base/16384/16402 /var/lib/pgsql/data-old/base/16384/16403 /var/lib/pgsql/data-old/base/16384/16404 /var/lib/pgsql/data-old/base/16384/16407 /var/lib/pgsql/data-old/base/16384/16409 /var/lib/pgsql/data-old/base/16384/16412 /var/lib/pgsql/data-old/base/16384/16445 /var/lib/pgsql/data-old/base/16384/16447 /var/lib/pgsql/data-old/base/16384/16448 /var/lib/pgsql/data-old/base/16384/16450 /var/lib/pgsql/data-old/base/16384/16451 /var/lib/pgsql/data-old/base/16384/16452 /var/lib/pgsql/data-old/base/16384/16453 /var/lib/pgsql/data-old/base/16384/16454 /var/lib/pgsql/data-old/base/16384/16457 /var/lib/pgsql/data-old/base/16384/16472 /var/lib/pgsql/data-old/base/16384/16474 /var/lib/pgsql/data-old/base/16384/16475 /var/lib/pgsql/data-old/base/16384/16477 /var/lib/pgsql/data-old/base/16384/16478 /var/lib/pgsql/data-old/base/16384/16479 /var/lib/pgsql/data-old/base/16384/16483 /var/lib/pgsql/data-old/base/16384/16485 /var/lib/pgsql/data-old/base/16384/16486 /var/lib/pgsql/data-old/base/16384/16487 /var/lib/pgsql/data-old/base/16384/16490 /var/lib/pgsql/data-old/base/16384/16518 /var/lib/pgsql/data-old/base/16384/16520 /var/lib/pgsql/data-old/base/16384/16521 /var/lib/pgsql/data-old/base/16384/16523 /var/lib/pgsql/data-old/base/16384/16524 /var/lib/pgsql/data-old/base/16384/16525 /var/lib/pgsql/data-old/base/16384/16526 /var/lib/pgsql/data-old/base/16384/16527 /var/lib/pgsql/data-old/base/16384/16528 /var/lib/pgsql/data-old/base/16384/16529 /var/lib/pgsql/data-old/base/16384/16532 /var/lib/pgsql/data-old/base/16384/16560 /var/lib/pgsql/data-old/base/16384/16562 /var/lib/pgsql/data-old/base/16384/16563 /var/lib/pgsql/data-old/base/16384/16565 /var/lib/pgsql/data-old/base/16384/16566 /var/lib/pgsql/data-old/base/16384/16567 /var/lib/pgsql/data-old/base/16384/16568 /var/lib/pgsql/data-old/base/16384/16569 /var/lib/pgsql/data-old/base/16384/16570 /var/lib/pgsql/data-old/base/16384/16571 /var/lib/pgsql/data-old/base/16384/16572 /var/lib/pgsql/data-old/base/16384/16575 /var/lib/pgsql/data-old/base/16384/16575_fsm /var/lib/pgsql/data-old/base/16384/16601 /var/lib/pgsql/data-old/base/16384/16603 /var/lib/pgsql/data-old/base/16384/16604 /var/lib/pgsql/data-old/base/16384/16606 /var/lib/pgsql/data-old/base/16384/16607 /var/lib/pgsql/data-old/base/16384/16608 /var/lib/pgsql/data-old/base/16384/16609 /var/lib/pgsql/data-old/base/16384/16610 /var/lib/pgsql/data-old/base/16384/16611 /var/lib/pgsql/data-old/base/16384/16612 /var/lib/pgsql/data-old/base/16384/16613 /var/lib/pgsql/data-old/base/16384/16614 /var/lib/pgsql/data-old/base/16384/16615 /var/lib/pgsql/data-old/base/16384/16620 /var/lib/pgsql/data-old/base/16384/16622 /var/lib/pgsql/data-old/base/16384/16629 /var/lib/pgsql/data-old/base/16384/16633 /var/lib/pgsql/data-old/base/16384/16633_fsm /var/lib/pgsql/data-old/base/16384/16642 /var/lib/pgsql/data-old/base/16384/16644 /var/lib/pgsql/data-old/base/16384/16645 /var/lib/pgsql/data-old/base/16384/16647 /var/lib/pgsql/data-old/base/16384/16648 /var/lib/pgsql/data-old/base/16384/16653 /var/lib/pgsql/data-old/base/16384/16655 /var/lib/pgsql/data-old/base/16384/16656 /var/lib/pgsql/data-old/base/16384/16657 /var/lib/pgsql/data-old/base/16384/16658 /var/lib/pgsql/data-old/base/16384/16665 /var/lib/pgsql/data-old/base/16384/16665_fsm /var/lib/pgsql/data-old/base/16384/16665_vm /var/lib/pgsql/data-old/base/16384/16677 /var/lib/pgsql/data-old/base/16384/16679 /var/lib/pgsql/data-old/base/16384/16680 /var/lib/pgsql/data-old/base/16384/16682 /var/lib/pgsql/data-old/base/16384/16683 /var/lib/pgsql/data-old/base/16384/16684 /var/lib/pgsql/data-old/base/16384/16687 /var/lib/pgsql/data-old/base/16384/16710 /var/lib/pgsql/data-old/base/16384/16712 /var/lib/pgsql/data-old/base/16384/16713 /var/lib/pgsql/data-old/base/16384/16715 /var/lib/pgsql/data-old/base/16384/16716 /var/lib/pgsql/data-old/base/16384/16717 /var/lib/pgsql/data-old/base/16384/16718 /var/lib/pgsql/data-old/base/16384/16719 /var/lib/pgsql/data-old/base/16384/16720 /var/lib/pgsql/data-old/base/16384/16721 /var/lib/pgsql/data-old/base/16384/16726 /var/lib/pgsql/data-old/base/16384/16730 /var/lib/pgsql/data-old/base/16384/16748 /var/lib/pgsql/data-old/base/16384/16750 /var/lib/pgsql/data-old/base/16384/16751 /var/lib/pgsql/data-old/base/16384/16753 /var/lib/pgsql/data-old/base/16384/16754 /var/lib/pgsql/data-old/base/16384/16755 /var/lib/pgsql/data-old/base/16384/16756 /var/lib/pgsql/data-old/base/16384/16757 /var/lib/pgsql/data-old/base/16384/16758 /var/lib/pgsql/data-old/base/16384/16759 /var/lib/pgsql/data-old/base/16384/16764 /var/lib/pgsql/data-old/base/16384/16766 /var/lib/pgsql/data-old/base/16384/16767 /var/lib/pgsql/data-old/base/16384/16768 /var/lib/pgsql/data-old/base/16384/16771 /var/lib/pgsql/data-old/base/16384/16782 /var/lib/pgsql/data-old/base/16384/16784 /var/lib/pgsql/data-old/base/16384/16785 /var/lib/pgsql/data-old/base/16384/16789 /var/lib/pgsql/data-old/base/16384/16806 /var/lib/pgsql/data-old/base/16384/16808 /var/lib/pgsql/data-old/base/16384/16809 /var/lib/pgsql/data-old/base/16384/16811 /var/lib/pgsql/data-old/base/16384/16812 /var/lib/pgsql/data-old/base/16384/16813 /var/lib/pgsql/data-old/base/16384/16814 /var/lib/pgsql/data-old/base/16384/16815 /var/lib/pgsql/data-old/base/16384/16816 /var/lib/pgsql/data-old/base/16384/16817 /var/lib/pgsql/data-old/base/16384/16820 /var/lib/pgsql/data-old/base/16384/16822 /var/lib/pgsql/data-old/base/16384/16823 /var/lib/pgsql/data-old/base/16384/16824 /var/lib/pgsql/data-old/base/16384/16827 /var/lib/pgsql/data-old/base/16384/16829 /var/lib/pgsql/data-old/base/16384/16830 /var/lib/pgsql/data-old/base/16384/16831 /var/lib/pgsql/data-old/base/16384/16834 /var/lib/pgsql/data-old/base/16384/16836 /var/lib/pgsql/data-old/base/16384/16837 /var/lib/pgsql/data-old/base/16384/16838 /var/lib/pgsql/data-old/base/16384/16841 /var/lib/pgsql/data-old/base/16384/16843 /var/lib/pgsql/data-old/base/16384/16844 /var/lib/pgsql/data-old/base/16384/16845 /var/lib/pgsql/data-old/base/16384/16848 /var/lib/pgsql/data-old/base/16384/16850 /var/lib/pgsql/data-old/base/16384/16851 /var/lib/pgsql/data-old/base/16384/16852 /var/lib/pgsql/data-old/base/16384/16855 /var/lib/pgsql/data-old/base/16384/16857 /var/lib/pgsql/data-old/base/16384/16858 /var/lib/pgsql/data-old/base/16384/16859 /var/lib/pgsql/data-old/base/16384/16862 /var/lib/pgsql/data-old/base/16384/16864 /var/lib/pgsql/data-old/base/16384/16865 /var/lib/pgsql/data-old/base/16384/16866 /var/lib/pgsql/data-old/base/16384/16869 /var/lib/pgsql/data-old/base/16384/16871 /var/lib/pgsql/data-old/base/16384/16872 /var/lib/pgsql/data-old/base/16384/16873 /var/lib/pgsql/data-old/base/16384/16876 /var/lib/pgsql/data-old/base/16384/16878 /var/lib/pgsql/data-old/base/16384/16879 /var/lib/pgsql/data-old/base/16384/16880 /var/lib/pgsql/data-old/base/16384/16883 /var/lib/pgsql/data-old/base/16384/16885 /var/lib/pgsql/data-old/base/16384/16886 /var/lib/pgsql/data-old/base/16384/16887 /var/lib/pgsql/data-old/base/16384/16890 /var/lib/pgsql/data-old/base/16384/16892 /var/lib/pgsql/data-old/base/16384/16893 /var/lib/pgsql/data-old/base/16384/16894 /var/lib/pgsql/data-old/base/16384/16897 /var/lib/pgsql/data-old/base/16384/16899 /var/lib/pgsql/data-old/base/16384/16900 /var/lib/pgsql/data-old/base/16384/16901 /var/lib/pgsql/data-old/base/16384/16904 /var/lib/pgsql/data-old/base/16384/16906 /var/lib/pgsql/data-old/base/16384/16907 /var/lib/pgsql/data-old/base/16384/16908 /var/lib/pgsql/data-old/base/16384/16911 /var/lib/pgsql/data-old/base/16384/16913 /var/lib/pgsql/data-old/base/16384/16914 /var/lib/pgsql/data-old/base/16384/16915 /var/lib/pgsql/data-old/base/16384/16918 /var/lib/pgsql/data-old/base/16384/16920 /var/lib/pgsql/data-old/base/16384/16921 /var/lib/pgsql/data-old/base/16384/16922 /var/lib/pgsql/data-old/base/16384/16925 /var/lib/pgsql/data-old/base/16384/16927 /var/lib/pgsql/data-old/base/16384/16928 /var/lib/pgsql/data-old/base/16384/16931 /var/lib/pgsql/data-old/base/16384/16939 /var/lib/pgsql/data-old/base/16384/16941 /var/lib/pgsql/data-old/base/16384/16942 /var/lib/pgsql/data-old/base/16384/16943 /var/lib/pgsql/data-old/base/16384/16944 /var/lib/pgsql/data-old/base/16384/16945 /var/lib/pgsql/data-old/base/16384/16946 /var/lib/pgsql/data-old/base/16384/16949 /var/lib/pgsql/data-old/base/16384/16951 /var/lib/pgsql/data-old/base/16384/16952 /var/lib/pgsql/data-old/base/16384/16955 /var/lib/pgsql/data-old/base/16384/16963 /var/lib/pgsql/data-old/base/16384/16965 /var/lib/pgsql/data-old/base/16384/16967 /var/lib/pgsql/data-old/base/16384/16968 /var/lib/pgsql/data-old/base/16384/16969 /var/lib/pgsql/data-old/base/16384/16970 /var/lib/pgsql/data-old/base/16384/16974 /var/lib/pgsql/data-old/base/16384/16975 /var/lib/pgsql/data-old/base/16384/16976 /var/lib/pgsql/data-old/base/16384/16983 /var/lib/pgsql/data-old/base/16384/16984 /var/lib/pgsql/data-old/base/16384/16985 /var/lib/pgsql/data-old/base/16384/16991 /var/lib/pgsql/data-old/base/16384/16992 /var/lib/pgsql/data-old/base/16384/16995 /var/lib/pgsql/data-old/base/16384/16999 /var/lib/pgsql/data-old/base/16384/17001 /var/lib/pgsql/data-old/base/16384/17005 /var/lib/pgsql/data-old/base/16384/17014 /var/lib/pgsql/data-old/base/16384/17016 /var/lib/pgsql/data-old/base/16384/17017 /var/lib/pgsql/data-old/base/16384/17019 /var/lib/pgsql/data-old/base/16384/17021 /var/lib/pgsql/data-old/base/16384/17023 /var/lib/pgsql/data-old/base/16384/17024 /var/lib/pgsql/data-old/base/16384/17027 /var/lib/pgsql/data-old/base/16384/17027_fsm /var/lib/pgsql/data-old/base/16384/17048 /var/lib/pgsql/data-old/base/16384/17050 /var/lib/pgsql/data-old/base/16384/17051 /var/lib/pgsql/data-old/base/16384/17053 /var/lib/pgsql/data-old/base/16384/17055 /var/lib/pgsql/data-old/base/16384/17056 /var/lib/pgsql/data-old/base/16384/17057 /var/lib/pgsql/data-old/base/16384/17058 /var/lib/pgsql/data-old/base/16384/17059 /var/lib/pgsql/data-old/base/16384/17060 /var/lib/pgsql/data-old/base/16384/17063 /var/lib/pgsql/data-old/base/16384/17070 /var/lib/pgsql/data-old/base/16384/17072 /var/lib/pgsql/data-old/base/16384/17076 /var/lib/pgsql/data-old/base/16384/17087 /var/lib/pgsql/data-old/base/16384/17089 /var/lib/pgsql/data-old/base/16384/17090 /var/lib/pgsql/data-old/base/16384/17092 /var/lib/pgsql/data-old/base/16384/17093 /var/lib/pgsql/data-old/base/16384/17099 /var/lib/pgsql/data-old/base/16384/17103 /var/lib/pgsql/data-old/base/16384/17103_fsm /var/lib/pgsql/data-old/base/16384/17121 /var/lib/pgsql/data-old/base/16384/17123 /var/lib/pgsql/data-old/base/16384/17124 /var/lib/pgsql/data-old/base/16384/17126 /var/lib/pgsql/data-old/base/16384/17127 /var/lib/pgsql/data-old/base/16384/17128 /var/lib/pgsql/data-old/base/16384/17129 /var/lib/pgsql/data-old/base/16384/17134 /var/lib/pgsql/data-old/base/16384/17138 /var/lib/pgsql/data-old/base/16384/17165 /var/lib/pgsql/data-old/base/16384/17167 /var/lib/pgsql/data-old/base/16384/17168 /var/lib/pgsql/data-old/base/16384/17170 /var/lib/pgsql/data-old/base/16384/17171 /var/lib/pgsql/data-old/base/16384/17172 /var/lib/pgsql/data-old/base/16384/17173 /var/lib/pgsql/data-old/base/16384/17174 /var/lib/pgsql/data-old/base/16384/17175 /var/lib/pgsql/data-old/base/16384/17176 /var/lib/pgsql/data-old/base/16384/17179 /var/lib/pgsql/data-old/base/16384/17183 /var/lib/pgsql/data-old/base/16384/17185 /var/lib/pgsql/data-old/base/16384/17186 /var/lib/pgsql/data-old/base/16384/17190 /var/lib/pgsql/data-old/base/16384/17207 /var/lib/pgsql/data-old/base/16384/17209 /var/lib/pgsql/data-old/base/16384/17210 /var/lib/pgsql/data-old/base/16384/17214 /var/lib/pgsql/data-old/base/16384/17224 /var/lib/pgsql/data-old/base/16384/17228 /var/lib/pgsql/data-old/base/16384/17238 /var/lib/pgsql/data-old/base/16384/17240 /var/lib/pgsql/data-old/base/16384/17241 /var/lib/pgsql/data-old/base/16384/17243 /var/lib/pgsql/data-old/base/16384/17246 /var/lib/pgsql/data-old/base/16384/17255 /var/lib/pgsql/data-old/base/16384/17257 /var/lib/pgsql/data-old/base/16384/17258 /var/lib/pgsql/data-old/base/16384/17260 /var/lib/pgsql/data-old/base/16384/17261 /var/lib/pgsql/data-old/base/16384/17262 /var/lib/pgsql/data-old/base/16384/17265 /var/lib/pgsql/data-old/base/16384/17267 /var/lib/pgsql/data-old/base/16384/17267_fsm /var/lib/pgsql/data-old/base/16384/17274 /var/lib/pgsql/data-old/base/16384/17276 /var/lib/pgsql/data-old/base/16384/17277 /var/lib/pgsql/data-old/base/16384/17279 /var/lib/pgsql/data-old/base/16384/17280 /var/lib/pgsql/data-old/base/16384/17281 /var/lib/pgsql/data-old/base/16384/17284 /var/lib/pgsql/data-old/base/16384/17309 /var/lib/pgsql/data-old/base/16384/17311 /var/lib/pgsql/data-old/base/16384/17312 /var/lib/pgsql/data-old/base/16384/17314 /var/lib/pgsql/data-old/base/16384/17315 /var/lib/pgsql/data-old/base/16384/17316 /var/lib/pgsql/data-old/base/16384/17317 /var/lib/pgsql/data-old/base/16384/17318 /var/lib/pgsql/data-old/base/16384/17319 /var/lib/pgsql/data-old/base/16384/17320 /var/lib/pgsql/data-old/base/16384/17323 /var/lib/pgsql/data-old/base/16384/17333 /var/lib/pgsql/data-old/base/16384/17335 /var/lib/pgsql/data-old/base/16384/17336 /var/lib/pgsql/data-old/base/16384/17338 /var/lib/pgsql/data-old/base/16384/17339 /var/lib/pgsql/data-old/base/16384/17340 /var/lib/pgsql/data-old/base/16384/17343 /var/lib/pgsql/data-old/base/16384/17347 /var/lib/pgsql/data-old/base/16384/17349 /var/lib/pgsql/data-old/base/16384/17350 /var/lib/pgsql/data-old/base/16384/17351 /var/lib/pgsql/data-old/base/16384/17354 /var/lib/pgsql/data-old/base/16384/17382 /var/lib/pgsql/data-old/base/16384/17384 /var/lib/pgsql/data-old/base/16384/17385 /var/lib/pgsql/data-old/base/16384/17387 /var/lib/pgsql/data-old/base/16384/17389 /var/lib/pgsql/data-old/base/16384/17390 /var/lib/pgsql/data-old/base/16384/17391 /var/lib/pgsql/data-old/base/16384/17392 /var/lib/pgsql/data-old/base/16384/17393 /var/lib/pgsql/data-old/base/16384/17394 /var/lib/pgsql/data-old/base/16384/17395 /var/lib/pgsql/data-old/base/16384/17396 /var/lib/pgsql/data-old/base/16384/17397 /var/lib/pgsql/data-old/base/16384/17398 /var/lib/pgsql/data-old/base/16384/17399 /var/lib/pgsql/data-old/base/16384/17400 /var/lib/pgsql/data-old/base/16384/17403 /var/lib/pgsql/data-old/base/16384/17403_fsm /var/lib/pgsql/data-old/base/16384/17413 /var/lib/pgsql/data-old/base/16384/17415 /var/lib/pgsql/data-old/base/16384/17416 /var/lib/pgsql/data-old/base/16384/17418 /var/lib/pgsql/data-old/base/16384/17419 /var/lib/pgsql/data-old/base/16384/17422 /var/lib/pgsql/data-old/base/16384/17422_fsm /var/lib/pgsql/data-old/base/16384/17435 /var/lib/pgsql/data-old/base/16384/17437 /var/lib/pgsql/data-old/base/16384/17438 /var/lib/pgsql/data-old/base/16384/17442 /var/lib/pgsql/data-old/base/16384/17451 /var/lib/pgsql/data-old/base/16384/17453 /var/lib/pgsql/data-old/base/16384/17454 /var/lib/pgsql/data-old/base/16384/17456 /var/lib/pgsql/data-old/base/16384/17461 /var/lib/pgsql/data-old/base/16384/17465 /var/lib/pgsql/data-old/base/16384/17473 /var/lib/pgsql/data-old/base/16384/17475 /var/lib/pgsql/data-old/base/16384/17477 /var/lib/pgsql/data-old/base/16384/17478 /var/lib/pgsql/data-old/base/16384/17479 /var/lib/pgsql/data-old/base/16384/17482 /var/lib/pgsql/data-old/base/16384/17500 /var/lib/pgsql/data-old/base/16384/17502 /var/lib/pgsql/data-old/base/16384/17503 /var/lib/pgsql/data-old/base/16384/17505 /var/lib/pgsql/data-old/base/16384/17507 /var/lib/pgsql/data-old/base/16384/17508 /var/lib/pgsql/data-old/base/16384/17509 /var/lib/pgsql/data-old/base/16384/17510 /var/lib/pgsql/data-old/base/16384/17513 /var/lib/pgsql/data-old/base/16384/17517 /var/lib/pgsql/data-old/base/16384/17519 /var/lib/pgsql/data-old/base/16384/17520 /var/lib/pgsql/data-old/base/16384/17522 /var/lib/pgsql/data-old/base/16384/17524 /var/lib/pgsql/data-old/base/16384/17527 /var/lib/pgsql/data-old/base/16384/17543 /var/lib/pgsql/data-old/base/16384/17545 /var/lib/pgsql/data-old/base/16384/17546 /var/lib/pgsql/data-old/base/16384/17548 /var/lib/pgsql/data-old/base/16384/17549 /var/lib/pgsql/data-old/base/16384/17550 /var/lib/pgsql/data-old/base/16384/17554 /var/lib/pgsql/data-old/base/16384/17556 /var/lib/pgsql/data-old/base/16384/17557 /var/lib/pgsql/data-old/base/16384/17559 /var/lib/pgsql/data-old/base/16384/17564 /var/lib/pgsql/data-old/base/16384/17568 /var/lib/pgsql/data-old/base/16384/17578 /var/lib/pgsql/data-old/base/16384/17580 /var/lib/pgsql/data-old/base/16384/17581 /var/lib/pgsql/data-old/base/16384/17583 /var/lib/pgsql/data-old/base/16384/17584 /var/lib/pgsql/data-old/base/16384/17585 /var/lib/pgsql/data-old/base/16384/17586 /var/lib/pgsql/data-old/base/16384/17589 /var/lib/pgsql/data-old/base/16384/17594 /var/lib/pgsql/data-old/base/16384/17598 /var/lib/pgsql/data-old/base/16384/17604 /var/lib/pgsql/data-old/base/16384/17606 /var/lib/pgsql/data-old/base/16384/17607 /var/lib/pgsql/data-old/base/16384/17609 /var/lib/pgsql/data-old/base/16384/17612 /var/lib/pgsql/data-old/base/16384/17614 /var/lib/pgsql/data-old/base/16384/17615 /var/lib/pgsql/data-old/base/16384/17617 /var/lib/pgsql/data-old/base/16384/17620 /var/lib/pgsql/data-old/base/16384/17626 /var/lib/pgsql/data-old/base/16384/17628 /var/lib/pgsql/data-old/base/16384/17629 /var/lib/pgsql/data-old/base/16384/17631 /var/lib/pgsql/data-old/base/12926/12883 /var/lib/pgsql/data-old/base/12926/12885 /var/lib/pgsql/data-old/base/12926/12749 /var/lib/pgsql/data-old/base/12926/12751 /var/lib/pgsql/data-old/base/1/12883 /var/lib/pgsql/data-old/base/1/12885 /var/lib/pgsql/data-old/base/1/12749 /var/lib/pgsql/data-old/base/1/12751 ok Setting next OID for new cluster ok Sync data directory to disk ok Creating script to analyze new cluster ok Creating script to delete old cluster ok Checking for hash indexes ok Upgrade Complete ---------------- Optimizer statistics are not transferred by pg_upgrade so, once you start the new server, consider running: ./analyze_new_cluster.sh Running this script will delete the old cluster's data files: ./delete_old_cluster.sh ``` so you should be able to pass the locale just like I did and use the upgrade method I used. You don’t even need to init Postgres on RL8 since Postgresql 9.2 is copied to the /var/lib/pgsql/data directory and upgraded accordingly. So I guess this command would work: ```auto PGSETUP_INITDB_OPTIONS="--locale=LATIN1" postgresql-setup upgrade ``` unless you need to set it to `-locale=C`. ---
**Author:** ![whichiso](https://avatars.discourse-cdn.com/v4/letter/w/838e76/32.png) [@whichiso](https://forums.rockylinux.org/u/whichiso)\ **Post date:** [November 5, 2021, 4:12pm UTC](https://forums.rockylinux.org/t/postgres-encoding-error-during-database-restore/4460/6 "2021-11-05T16:12:48Z")
> [@iwalker](#): > > Since by default it wanted to do en\_US-UTF-8 on my system, but I needed en\_GB-UTF-8, I needed to pass the parameters like this: > > ```auto > [root@rocky ~]# PGSETUP_INITDB_OPTIONS="--locale=en_GB.UTF-8" postgresql-setup upgrade > > ``` @iwalker Thanks for the detailed reply. Did you at first run initdb during your setup and thus had to delete the `/var/lib/pgsql/data` directory and then copy over your 9.2 data directory? Just checking if that’s all, or if I must uninstall postgresql-server first and then install it fresh. If I use `PGSETUP_INITDB_OPTIONS="--locale=en_US.UTF-8" postgresql-setup upgrade` will it update the databases from Latin1 to UTF8? (Just being hopeful). ---
**Author:** ![iwalker](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.rockylinux.org/iwalker/32/2599_2.png) [@iwalker](https://forums.rockylinux.org/u/iwalker)\ **Post date:** [November 5, 2021, 4:20pm UTC](https://forums.rockylinux.org/t/postgres-encoding-error-during-database-restore/4460/7 "2021-11-05T16:20:03Z")
> [@whichiso](#): > > Did you at first run initdb during your setup and thus had to delete the `/var/lib/pgsql/data` directory and then copy over your 9.2 data directory? If you already have done an init on /var/lib/pgsql/data, then just delete it and replace it with the data directory from your CentOS7 install. Make sure the permissions are the same or if not chown the files to fix it. Alternatively, you can rename data to data10 for example (I did this), and then copied from my old server (rhel7). You would need to use `PGSETUP_INITDB_OPTIONS="--locale=latin1"` based on the error you got when it didn’t match en\_US.UTF-8 previously (either that or you need `--locale=C` experiment until it works and you don’t get a locale error. If it doesn’t match the upgrade won’t continue anyway. Your new database will then be the same format as the old one - which is needed for successful upgrade. ---
**Author:** ![whichiso](https://avatars.discourse-cdn.com/v4/letter/w/838e76/32.png) [@whichiso](https://forums.rockylinux.org/u/whichiso)\ **Post date:** [November 5, 2021, 7:50pm UTC](https://forums.rockylinux.org/t/postgres-encoding-error-during-database-restore/4460/8 "2021-11-05T19:50:31Z")
@iwalker Is there an advantage doing your well detailed copy data folder and postgresql-setup upgrade, over initializing as psotgres user, `initdb --locale=C -D /var/lib/pgsql/data`, which allows --locale=C and then restoring the databases from a backup `psql -U postgres -f pg_dbs.bkp postgres`? ---
**Author:** ![iwalker](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.rockylinux.org/iwalker/32/2599_2.png) [@iwalker](https://forums.rockylinux.org/u/iwalker)\ **Post date:** [November 5, 2021, 8:07pm UTC](https://forums.rockylinux.org/t/postgres-encoding-error-during-database-restore/4460/9 "2021-11-05T20:07:34Z")
One advantage, is it’s quicker. I don’t know if your dump included the postgres database as well, but if not, then the method I listed upgrades all databases, including postgres, which means I have all my database users as well. Just depends what you feel comfortable with. If you prefer database dumps, then use them. This worked for me, so I have no need for trying database dumps. I also used a pg\_upgrade on a Debian system recently between postgres 12 and 13. Also upgrades that data directory. ---
**Author:** ![whichiso](https://avatars.discourse-cdn.com/v4/letter/w/838e76/32.png) [@whichiso](https://forums.rockylinux.org/u/whichiso)\ **Post date:** [November 8, 2021, 12:09am UTC](https://forums.rockylinux.org/t/postgres-encoding-error-during-database-restore/4460/10 "2021-11-08T00:09:29Z")
@iwalker things do not go so well. I renamed the data folder in /var/lib/pgsql, copied my data folder to it from my CentOS7 box, set permissions on the new data folder to match the original data folder. I stopped the postgresql service then ran the PGSETUP command. The upgrade log file is empty, thus no insight on what went wrong. > [root@finaidrocky pgsql]# ls -la > total 24 > drwx------ 5 postgres postgres 153 Nov 7 16:03 . > drwxr-xr-x. 65 root root 4096 Nov 3 09:12 … > -rw------- 1 postgres postgres 497 Nov 7 16:00 .bash\_history > -rw-r–r-- 1 postgres postgres 85 Jun 10 14:37 .bash\_profile > drwx------ 2 postgres postgres 6 Nov 3 09:16 .cache > drwx------ 15 postgres postgres 4096 Nov 7 16:03 data > drwx------ 20 postgres postgres 4096 Nov 7 16:03 data-original-initdb > -rw------- 1 postgres postgres 687 Nov 4 10:01 .psql\_history > -rw------- 1 postgres postgres 0 Nov 7 16:03 upgrade\_postgresql.log > [root@finaidrocky pgsql]# `PGSETUP_INITDB_OPTIONS="--locale=C" postgresql-setup upgrade` > WARNING: using obsoleted argument syntax, try --help > WARNING: arguments transformed to: postgresql-setup --upgrade --unit postgresql > > - Upgrading database. > ERROR: The pidfile ‘/var/lib/pgsql/data-old/postmaster.pid’ exists. Verify that there is no postmaster > running the /var/lib/pgsql/data-old directory. > ERROR: Upgrade failed. > - See /var/lib/pgsql/upgrade\_postgresql.log for details. ---
**Author:** ![iwalker](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.rockylinux.org/iwalker/32/2599_2.png) [@iwalker](https://forums.rockylinux.org/u/iwalker)\ **Post date:** [November 8, 2021, 5:55am UTC](https://forums.rockylinux.org/t/postgres-encoding-error-during-database-restore/4460/11 "2021-11-08T05:55:42Z")
Stop postgres on the server and delete the pid file ---
**Author:** ![whichiso](https://avatars.discourse-cdn.com/v4/letter/w/838e76/32.png) [@whichiso](https://forums.rockylinux.org/u/whichiso)\ **Post date:** [November 8, 2021, 4:04pm UTC](https://forums.rockylinux.org/t/postgres-encoding-error-during-database-restore/4460/12 "2021-11-08T16:04:01Z")
@iwalker the upgrade process just won’t work for me. I shutdown postgresql with `systemctl stop postgresql`, recopied my centos7 data folder (just to start fresh), deleted the postamster.pid file from the data folder. I tagged `upgrade` with` --` to prevent the “using obsoleted warning.” > [root@finaidrocky pgsql]# PGSETUP\_INITDB\_OPTIONS=“`--`locale=C” postgresql-setup --upgrade > > - Upgrading database. > ERROR: pg\_upgrade tool failed > ERROR: Upgrade failed. > - See /var/lib/pgsql/upgrade\_postgresql.log for details. > > upgrade\_postgresql.log states the cluster was not cleanly shutdown. > The source cluster was not shut down cleanly. ---
**Author:** ![iwalker](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.rockylinux.org/iwalker/32/2599_2.png) [@iwalker](https://forums.rockylinux.org/u/iwalker)\ **Post date:** [November 8, 2021, 4:21pm UTC](https://forums.rockylinux.org/t/postgres-encoding-error-during-database-restore/4460/13 "2021-11-08T16:21:28Z")
And postgres was shutdown properly on your original server before copying? Considering the error message in your post, either postgres was still running when you copied it, or it either wasn’t shut down properly or some other issue is causing you problems. You are likely going to have to do some maintenance tasks on that, or restart and shutdown postgres cleanly before copying to the new machine. It cannot be copied from the old server if postgres is still running. If not, and you still have problems, then something is obviously unique to your setup which is causing you issues. Since, this should **just** work. ---
**Author:** ![whichiso](https://avatars.discourse-cdn.com/v4/letter/w/838e76/32.png) [@whichiso](https://forums.rockylinux.org/u/whichiso)\ **Post date:** [November 8, 2021, 6:39pm UTC](https://forums.rockylinux.org/t/postgres-encoding-error-during-database-restore/4460/14 "2021-11-08T18:39:43Z")
@iwalker That was it. I failed to shutdown postgresql on the centos7 box prior to copying the data folder. Once I did that the upgrade worked. ---
**Author:** ![brian](https://sea2.discourse-cdn.com/flex020/user_avatar/forums.rockylinux.org/brian/32/5777_2.png) [@brian](https://forums.rockylinux.org/u/brian)\ **Post date:** [August 25, 2023, 4:02am UTC](https://forums.rockylinux.org/t/postgres-encoding-error-during-database-restore/4460/15 "2023-08-25T04:02:11Z")
Closed on August 25, 2023, 4:02am UTC