pg_upgrade from PostgreSQL 9.3.6 to 11.14

Hi,
The  pg_upgrade as well as other database applications/commands are in the /bin directory like this

C:\Program Files\PostgreSQL\11.14\bin

Select allOpen in new window

by default if you don't choose alternative installation path or <the path where you installed postgreSQL>/bin .

Regards,
    Tomas Helgi

tks.

So this mean if I have UPGRADE the PostgreSQL 9.3.6 on the SAME box, I have to install the 11.14 on the SAME box but different path, then run the pg_upgrade from the 11.14 binary which is installed, then it will reference to the existing 9.3.6 data path and upgrade all table/database meta accordingly ?

and finally the data path STILL be the same one as the old 9.3.6 ?

The old and new PostgreSQL have separate binary and data directories to be able to revert to the old version if needed.
The upgrade process copies and makes necessary changes on a new location. Application databases are usually copied as is but the catalog database is changed in the process.

https://www.postgresql.org/docs/11/pgupgrade.html

You need to make sure that you have enough disk/space to do the upgrade. To emphasize, if you have 1 TB database cluster you need to have at least 1TB free space on the host either on the same or separate disk.

Regards,
    Tomas Helgi

The upgrade process copies and makes necessary changes on a new location.

So the upgrade process will upgrade and help to COPY all necessary data from old data path to new path ?

 Application databases are usually copied as is but the catalog database is changed in the process.

catalog database will also be copy to the new path AUTOMATICALLY ?

and command is, e.g.:

on linux, but we are on Windows.

pg_upgrade --old-bindir=/usr/pgsql-9.3/bin/ --new-bindir=/usr/pgsql-11/bin/ --old-datadir=/var/lib/pgsql/9.3/data/ --new-datadir=/var/lib/pgsql/11/data/ 

Select allOpen in new window

only?

The old and new PostgreSQL have separate binary and data directories to be able to revert to the old version if needed.

ok, install the new PostgreSQL on new path any way first !

So the upgrade process will upgrade and help to COPY all necessary data from old data path to new path ? 

Yes.

catalog database will also be copy to the new path AUTOMATICALLY ?

Yes.
The command is the same but the paths are in a "Windows style format" e.g

pg_upgrade.exe        
--old-datadir "D:/PostgreSQL/9.6/data"        
--new-datadir "E:/PostgreSQL/11/data"        
--old-bindir "C:/Program Files/PostgreSQL/9.6/bin"        
--new-bindir "C:/Program Files/PostgreSQL/11/bin"

Select allOpen in new window

In the above example the data is on D disk and the new data disk is on E while the C disk have the binaries.

Regards,
    Tomas Helgi

I would also consider to run everything in Windows as "Administrator" meaning all installs, etc should be executed by right click and "Run as Administrator" and also to run first a test pg_upgrade that will give you any potential issues/errors by using the "...-c -k" switches at nthe end of your upgrade command. So it would be something like:

pg_upgrade --old-bindir="C:/Program Files/PostgreSQL/9.6/bin" --new-bindir="C:/Program Files/PostgreSQL/11/bin" --old-datadir="D:/PostgreSQL/9.6/data" --new-datadir="E:/PostgreSQL/11/data" -c

Select allOpen in new window

Also always run the pg_upgrade utility from the LATEST version - meaning run it from the version 11 that you will install and not the old/existing 9.6 path and make sure you "Run as Administrator" this pg_upgrade command.
Also...you could use the "-k" switch to only "link" the directories without copying the data ("use hard links instead of copying files to the new cluster" from here https://www.postgresql.org/docs/10/pgupgrade.html) as this will reduce the pg_upgrade time itself and the amount of space needed. The draw-back is that once upgraded you can't roll back to use the old 9.6 version on that same server unless...you took a snapshot of the data directory before the upgrade.

  

lcohan ,

I would also consider to run everything in Windows as "Administrator" meaning all installs, etc should be executed by right click and "Run as Administrator"  

you mean when running the command prompt?

"...-c -k" switches

tks. but what is that mean?

also there are no GUI version of pg_upgrade, right? must use command !

Also always run the pg_upgrade utility from the LATEST version - meaning run it from the version 11 that you will install and not the old/existing 9.6 path and make sure you "Run as Administrator" this pg_upgrade command.

yeah, make sense!

Also...you could use the "-k" switch to only "link" the directories without copying the data ("use hard links instead of copying files to the new cluster" from here https://www.postgresql.org/docs/10/pgupgrade.html) as this will reduce the pg_upgrade time itself and the amount of space needed

good point! and how many times you use this method and how many time it doesn't works ?

The draw-back is that once upgraded you can't roll back to use the old 9.6 version on that same server unless...you took a snapshot of the data directory before the upgrade.

yeah! this is the concern.

hi both,

one thing I do'nt understand is, if both version install on the same machine, how can I choose which PostgreSQL to use/start?

once installed 11.14 on 9.3.6 box there will be 2x PostgreSQL instance ?

"Run as Administrator"  should be used for everything - software installations included on Windows in order to avoid any errors issues due to ownership. In my case 99% are LINUX servers where all is executed as root+postgres user so we had no issues but in windows you should run all as administrator which has similar rights to root.

good point! and how many times you use this method and how many time it doesn't works ?  

I've used pg_upgrade in order to perform tens of servers*various versions from 7.* up to 13 without any of them failing so far just because we always used the test upgrades with -c ( and -k in most of the cases) and solved all of the issues prior to the actual upgrade which always worked fine for us. Mind you we always did an upgrade in dev/qa first and tested the apps to make sure all work fine against the new version. BTW...as noted here https://github.com/vrana/adminer/pull/367/commits/717d9e636e6eb654949a6ed16407ab7288ff5205 Postgres dropped own system adsrc column which caused some of our ruby code to fail and could have been a mini disaster unless tested/caught in QA so...one other thing to check is to make sure existing/old config's work with new versions as this could be another factor in your upgrades - never just copy over an existing config file but transfer the settings from current to new version config file as if you copiy it you may loose settings/configurables from newer version - been there bone that messed up new version postgres but again was caught in dev/qa.
For the failback we had SAN snapshots taken after we went down just prior to upgrade so we could roll back if needed to start a previous version if new one proves impossible to use right after coming up on it in prod server.

one thing I do'nt understand is, if both version install on the same machine, how can I choose which PostgreSQL to use/start?

At high level your upgrade process steps would be:

1. Take full backup and/or SAN backup of the current Postgres data folder - D:/PostgreSQL/9.6/data in your case.
2. Shutdown all apps touching Postgres server to be upgraded. 
3. Stop your current Postgres 9.6 server
4. Copy over your  pg_hba.conf file and transfer all configs/settings from the existing version  postgresql.conf  to new postgres.conf file from v.11
5. Assuming you have installed the new PostgreSQL version in the right place/dirs you should initialize the new data directory by uing initdb run it as postgres user.
6. Stop both Postgres versions 9.6 and 11. To stop Postgres you can use pg_ctl.exe utility like described at link below - just use the word "stop" instead of "start"
7. Run the command with -c -k to confirm your server is ready for upgrade and fix any/all issues that may be reported
8. After successful/clean run of the test pg_upgrade  you can perform the actual upgrade without the -c switch
9. After successful upgrade you can start the new Postgres server and then execute the maintenance that is concluding the actual upgrade - you get message about it at the end of the upgrade and essentially is a vacuum + analyze on all upgraded db's. To start Postgres you can use pg_ctl.exe utility like described at link below.
10. Test your apps against new version
11. Start your apps and go live on new version
12. Perform a backup as soon as possible on the new version.

Here https://gist.github.com/jctosta/baa4a1ba472a5999f445c0f43fdbe208 are the windows commands to be used for initdb, stat, stop Postgres  and etc...related to Windows. And here https://www.postgresql.org/docs/11/app-initdb.html is the initdb doc from Postgres 11

I've used pg_upgrade in order to perform tens of servers*various versions from 7.* up to 13 without any of them failing so far just because we always used the test upgrades with -c ( and -k in most of the cases) and solved all of the issues prior to the actual upgrade which always worked fine for us.  

so the command is, e.g.:

pg_upgrade --old-bindir="C:/Program Files/PostgreSQL/9.6/bin" --new-bindir="C:/Program Files/PostgreSQL/11/bin" 
--old-datadir="D:/PostgreSQL/9.6/data" --new-datadir="E:/PostgreSQL/11/data" -c -k

Select allOpen in new window

by  the link:
https://www.postgresql.org/docs/10/pgupgrade.htm 

-c
--check

check clusters only, don't change any data

so this parameter is about  NOT CHANGING/UPGRADE the existing/OLD PostgreSQL at all, but just check everything need for the upgrade for us ?

so it will show a report tell us what is missing before the real upgrade?

and after we fix everything we proceed with:

pg_upgrade --old-bindir="C:/Program Files/PostgreSQL/9.6/bin" --new-bindir="C:/Program Files/PostgreSQL/11/bin" 
--old-datadir="D:/PostgreSQL/9.6/data" --new-datadir="E:/PostgreSQL/11/data" -k

Select allOpen in new window

which is without the -c ?

and currently our postgreSQL is run under the application name AlfrescoPostgreSQL and service properties is:
D:\Alfresco\postgresql\bin\pg_ctl.exe" runservice -N "alfrescoPostgreSQL" -D "D:/Alfresco/alf_data/postgresql

so data path should be "D:/Alfresco/alf_data/postgresql"  ? and it seems we don't have something like 

/PostgreSQL/9.6/data

Select allOpen in new window

and binary path is :

D:\Alfresco\postgresql\bin

Select allOpen in new window

?

how can we know which path is the right one ?

so...one other thing to check is to make sure existing/old config's work with new versions as this could be another factor in your upgrades - never just copy over an existing config file but transfer the settings from current to new version config file as if you copiy it you may loose settings/configurables from newer version

The upgrade is for the an application called alfresco, and we just follow application requirement on the DB and OS they support.

BTW, what configuration are you talking about ? is it a file ?

Copy over your  pg_hba.conf file and transfer all configs/settings from the existing version  postgresql.conf  to new postgres.conf file from v.11

this is another interesting point I never seen here before, I just know I need to run the pg_upgrade and that's all, and now it seems it is not.

Assuming you have installed the new PostgreSQL version in the right place/dirs you should initialize the new data directory by uing initdb run it as postgres user.

initdb, another new thing for me, what will happen if we do not do that?

Run the command with -c -k to confirm your server is ready for upgrade and fix any/all issues that may be reported

I think this one is the first steps to do as it is CHECK only, it is not going to change anything, right?

now I just install PostgreSQL 11.13 and it always shows this error :



any idea?

in windows event log I only see this :

A fatal alert was generated and sent to the remote endpoint. This may result in termination of the connection. The TLS protocol defined fatal error code is 40. The Windows SChannel error state is 808.

Hi,

-c
--check

check clusters only, don't change any data

so this parameter is about  NOT CHANGING/UPGRADE the existing/OLD PostgreSQL at all, but just check everything need for the upgrade for us ?

so it will show a report tell us what is missing before the real upgrade?

Yes.

how can we know which path is the right one ? 

Note that some examples in the links I provided are for Linux but it applies to Windows as well. Just a matter of how you write the paths to the binary and data directories. You are running it on Windows so every path starts with a drive letter.  ;)

IF the -k parameter is used then you are working on the same data directory otherwise if -k is skipped then you have different source/dest data directories. And as mentioned in above comments you loose the ability to revert to the old version after upgrade.

Regards,
   Tomas Helgi

it seems the 11.13, 11.24 binary I download from postgreSQL site is not working, what is the correct path for me to download 11.4  ?

It looks like the only options for Windows https://www.postgresql.org/download/windows/ are to get them via EnterpriseDB either via their "Interactive installer by EDB Download the installer certified by EDB for all supported PostgreSQL versions."
OR
you can get the "Binaries from installer Version 11.14 " but still from EnterpriseDB: https://www.enterprisedb.com/download-postgresql-binaries

It looks like the only options for Windows https://www.postgresql.org/download/windows/ are to get them via EnterpriseDB either via their "Interactive installer by EDB Download the installer certified by EDB for all supported PostgreSQL versions."
OR

this is what I keep trying today ! I tried many vesrion ! all can't create the PostgreSQL service in Windows, so when uninstalling it complain can't find service to remove.

tomorrow will try other machine to install the same binary .

As noted on that download site:

If you have issues with the website it's hosted on, please contact webmaster@enterprisedb.com. 

I would send them an email.
QQ...did you right click the installer postgresql-11.14-1-windows-x64.exe and select "Run as Administrator"?

I just downloaded postgresql-11.14-1-windows-x64.exe package and installed it successfully on my Windows10 desktop as seen above so...

Hi,

Did you run the installer with "Run as Administrator" ? Windows security might prevent some of the installation steps to proceed successfully if you don't run it as Administrator.

Same goes for the pg_upgrade process. Run it in an elevated cmd window.

Regards,
    Tomas Helgi

ok will try again, I remember i did !

BTW, if the installer FIND my permission is not ok, it should prompt instead of letting me finish the installer with error, right ?

Did you run the installer with "Run as Administrator" ? 

yes! I did





What if we can't install the postgreSQL on the SAME box as the old postgreSQL, we will have no choice to install PostgreSQL on another new VM, when upgrading:

pg_upgrade --old-bindir="C:/Program Files/PostgreSQL/9.6/bin" --new-bindir="C:/Program Files/PostgreSQL/11/bin" --old-datadir="D:/PostgreSQL/9.6/data" --new-datadir="E:/PostgreSQL/11/data" -k

can the --old-bindir and -old-datadir point to a remote windows server path ? but the path specify in --old-bindir and -old-datadir  must share right?

today we found out something straight from the installation: As long as when we are installing PostgreSQL and the collate is NOT the default one but other, it can't create the windows service!







if it is install by default collate then everything will be fine !!

So once installed how can we change the collate of the postgreSQL installation ?

I read this : https://www.postgresql.org/docs/10/sql-altercollation.html

I can't run this script:

SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
       pg_describe_object(classid, objid, objsubid) AS "Object"
  FROM pg_depend d JOIN pg_collation c
       ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
  WHERE c.collversion <> pg_collation_actual_version(c.oid)
  ORDER BY 1, 2;

can I know if I have to run all command shown in the link above: 

ALTER COLLATION name REFRESH VERSION 
ALTER COLLATION name RENAME TO new_name
ALTER COLLATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER COLLATION name SET SCHEMA new_schemaALTER COLLATION 

Select allOpen in new window

this is the collation we have in PROD using
:
SELECT
*
FROM pg_collation;



the one installed with default collation is having something like that.

lcohan ,

you can get the "Binaries from installer Version 11.14 " but still from EnterpriseDB: https://www.enterprisedb.com/download-postgresql-binaries 

and this installation is different from the installer from PostgreSQL:


and I follow this:
https://www.youtube.com/watch?v=_OsTI0TSXMM&ab_channel=TechKENIN

to install both 9.4.x and 11.14.

but now, the funny thing is, 9.4.x can use the collation I want but not 11.14, collation problem still exists, please tell me how to fix collation.

You should be able to do that change in C:\Program Files\PostgreSQL\11\data\postgres.conf file - edit in notepad, find "lc_" and change all those to the desired collation then restart the Postgres11. In my case I had to add the lc_collate to the list and changed all from "default installed"

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'English_Canada.1252'         # locale for system error message
               # strings
lc_monetary = 'English_Canada.1252'         # locale for monetary formatting
lc_numeric = 'English_Canada.1252'         # locale for number formatting
lc_time = 'English_Canada.1252'            # locale for time formatting

Select allOpen in new window

to:

lc_messages en_US.UTF-8
lc_monetary en_US.UTF-8
lc_numeric en_US.UTF-8
lc_time en_US.UTF-8
lc_collate en_US.UTF-8

Select allOpen in new window

hi,

# These settings are initialized by initdb, but they can be changed. lc_messages = 'English_Canada.1252'         # locale for system error message                # strings lc_monetary = 'English_Canada.1252'         # locale for monetary formatting lc_numeric = 'English_Canada.1252'         # locale for number formatting lc_time = 'English_Canada.1252'            # locale for time formatting

can only be change in postgres.conf  ? I think I need to use PostgreSQL script!

any URL shows the mapping of collation?

I want to change to this:



what should these be:
lc_monetary lc_numeric lc_time lc_collate 

?

can only be change in postgres.conf  ? I think I need to use PostgreSQL script! 

You can see here https://www.postgresql.org/docs/9.5/config-setting.html the various ways to update postgres.conf where psql commands are included.

As far as Hong Kong SAR.950...I'm not sure what Postgres collation matches that Windows system but you can ask the Postgres comunity and hopefully there's a matching one.
But have a look at your 9.6 postgres.conf file or run:

show all;

Select allOpen in new window

psql command as superuser and you'll see how they are set in current 9.x and set them the same in 11

psql command as superuser and you'll see how they are set in current 9.x and set them the same in 11

this can be the one to go, they are the SAME on all edition?

but one thing amaze me, this is from 9.4.x:

datestyle = 'iso, dmy'
#intervalstyle = 'postgres'
timezone = 'Asia/Hong_Kong'
#timezone_abbreviations = 'Default'     # Select the set of available time zone
               # abbreviations.  Currently, there are
               #   Default
               #   Australia (historical usage)
               #   India
               # You can create your own file in
               # share/timezonesets/.
#extra_float_digits = 0         # min -15, max 3
#client_encoding = sql_ascii      # actually, defaults to database
               # encoding

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'Chinese (Traditional)_Hong Kong SAR.950'         # locale for system error message
               # strings
lc_monetary = 'Chinese (Traditional)_Hong Kong SAR.950'         # locale for monetary formatting
lc_numeric = 'Chinese (Traditional)_Hong Kong SAR.950'         # locale for number formatting
lc_time = 'Chinese (Traditional)_Hong Kong SAR.950'            # locale for time formatting

# default configuration for text search
default_text_search_config = 'pg_catalog.simple'

# - Other Defaults -

#dynamic_library_path = '$libdir'
#local_preload_libraries = ''
#session_preload_libraries = ''

Select allOpen in new window

and this is from 11.14

datestyle = 'iso, dmy'
#intervalstyle = 'postgres'
timezone = 'Asia/Shanghai'
#timezone_abbreviations = 'Default'     # Select the set of available time zone
               # abbreviations.  Currently, there are
               #   Default
               #   Australia (historical usage)
               #   India
               # You can create your own file in
               # share/timezonesets/.
#extra_float_digits = 0         # min -15, max 3
#client_encoding = sql_ascii      # actually, defaults to database
               # encoding

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'Chinese (Traditional)_Hong Kong SAR.950'         # locale for system error message
               # strings
lc_monetary = 'Chinese (Traditional)_Hong Kong SAR.950'         # locale for monetary formatting
lc_numeric = 'Chinese (Traditional)_Hong Kong SAR.950'         # locale for number formatting
lc_time = 'Chinese (Traditional)_Hong Kong SAR.950'            # locale for time formatting

# default configuration for text search
default_text_search_config = 'pg_catalog.simple'

# - Shared Library Preloading -

#shared_preload_libraries = ''   # (change requires restart)
#local_preload_libraries = ''
#session_preload_libraries = ''
#jit_provider = 'llvmjit'      # JIT library to use

# - Other Defaults -

#dynamic_library_path = '$libdir'

Select allOpen in new window

very little different, right?

only:
1) timezone is not the same

only change this can fix that ?  SEEMS a yes !

so PostgreSQL allow us to change collation after installation and initdb.exe ?

right now we have the postgreSQL database like that:


by this:

pg_upgrade --old-bindir=/usr/pgsql-9.3/bin/ --new-bindir=/usr/pgsql-11/bin/ --old-datadir=/var/lib/pgsql/9.3/data/ --new-datadir=/var/lib/pgsql/11/data/  

do you think the upgrade will helps to create the alfresco DB automatically on the TARGET ?

and I tried to run the pg_upgrade from 9.3.16 to 9.4.26 by :

pg_upgrade --old-bindir="D:\Alfresco-b\postgresql\bin" --new-bindir="D:\postgresql-9.4.26\pgsql\bin" --old-datadir="D:\Alfresco\alf_data\postgresql" --new-datadir="D:\postgresql-9.4.26\pgsql\data" -c -k

Select allOpen in new window

it prompts an error:

D:\PostgreSQL_tem>D:\postgresql-9.4.26\pgsql\bin\pg_upgrade --old-bindir="D:\Alf
resco-b\postgresql\bin" --new-bindir="D:\postgresql-9.4.26\pgsql\bin" --old-data
dir="D:\Alfresco\alf_data\postgresql" --new-datadir="D:\postgresql-9.4.26\pgsql\
data" -c -k
Performing Consistency Checks
-----------------------------
Checking cluster versions
Old cluster data and binary directories are from different major versions.
Failure, exiting

Select allOpen in new window

any idea ?

I intended to upgrade from PostgreSQL 9.3.6, compiled by Visual C++ build 1600, 64-bit to PostgreSQL 9.4.26, compiled by Visual C++ build 1800, 64-bit

it is not a major version upgrade, right?

closest match is :
https://gist.github.com/giannisp/ebaca117ac9e44231421f04e7796d5ca

This is perfect thank you @giannisp!
For anyone else that encounters Old cluster data and binary directories are from different major versions. - check that cat $dir/PG_VERSION and pg_upgrade -V match for old and new.
Anyone that has ussies with the locale, try initdb with --locale. e.g.:

initdb --locale=en_US /usr/local/var/postgres -E utf8

Select allOpen in new window

/cc @MTCoster for version issues :)

but I don't understand what it means

or should I say from 9.3.16 to 9.4.26, we don't use pg_upgrade as it is ONLY use for big version/major verison upgrade, e.g. 9 .x.x. to 10.x.x., or 9.x.x. to 11.x.x ?

"they are the SAME on all edition "

I'm not sure what are you asking about being the "same" but if it is about the Postgres configuration settings the answer is NO they are not always the same as Postgres.conf file content may change from version to version as needed that's why I said you should "port/move" ANY custom values you have in current server Postgres.conf to the new version instead of copying over the file from old version/dir to the new version/dir.

"do you think the upgrade will helps to create the alfresco DB automatically on the TARGET ? "

Please don't get me wrong but...you may need a DBA to help you with this upgrade as it seems to me that you have a lot to read/learn. Of course that database any any other user databases would be "...automatically on the TARGET " or new new version server as what you are trying to run is a in-place upgrade from old to new version and even more so "-k" switch will not literally copy all data from old to new version/directory but use a link to do that as I already mentioned:

-k
--link

use hard links instead of copying files to the new cluster

all from here: https://www.postgresql.org/docs/10/pgupgrade.html

psql command as superuser and you'll see how they are set in current 9.x and set them the same in 11

as you said I should set the SAME thing in 9.x. to 11.14, so I am not sure if collation is configuring in the same way between version.

and it seems I have to PORT carefully about the setting.

Please don't get me wrong but...you may need a DBA to help you with this upgrade as it seems to me that you have a lot to read/learn

only myself here for this project and I come from MS SQL and MySQL background, PostgreSQL config in this way a bit similiar to MysQL.

ou are trying to run is a in-place upgrade from old to new version a

this should not be consider as in-place upgrade right?  both PostgreSQL run on the SAME host (perfectly speaking ) and after pg_upgrade done, if I don't use -k, another copy of database files will be create on the target, right?

Checking cluster versions Old cluster data and binary directories are from different major versions. Failure, exiting

how about this error message, what should I do to solve this problem BEFORE I use pg_dumpall to backup and restore to the new PostgreSQL instance?

actually what will be the different between the target migrate/upgrade by pg_upgrade and pg_dumpall ?

today I try again on the pg_upgrade

and this is from the pg_upgrade_server_start.log:

command: "D:\Alfresco-b\postgresql\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "D:\Alfresco\alf_data\postgresql" -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000 " start >> "pg_upgrade_server_start.log" 2>&1
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.


command: "D:\Program Files\PostgreSQL\9.5\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "D:\Program Files\PostgreSQL\9.5\data" -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000 -c synchronous_commit=off -c fsync=off -c full_page_writes=off -c vacuum_defer_cleanup_age=0 " start >> "pg_upgrade_server_start.log" 2>&1
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start.... done
server started

Select allOpen in new window

command: "D:\Alfresco-b\postgresql\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "D:\Alfresco\alf_data\postgresql" -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000 " start >> "pg_upgrade_server_start.log" 2>&1
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.

can't see why, I tried the pg_upgrade from 9.5 (I now installed 9.5,11.14 and 13 on the same box, it is working fine now!)

and from pg_upgrade_server.log:

LOG:  skipping missing configuration file "D:/Alfresco/alf_data/postgresql/postgresql.auto.conf"
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.3, which is not compatible with this version 9.4.12.
2021-11-19 15:20:53 HKT LOG:  redirecting log output to logging collector process
2021-11-19 15:20:53 HKT HINT:  Future log output will appear in directory "pg_log".

Select allOpen in new window

LOG:  skipping missing configuration file "D:/Alfresco/alf_data/postgresql/postgresql.auto.conf"
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.3, which is not compatible with this version 9.4.12.


the postgreSQL from Alfresco application is not a standard binary/configuaration so it can be upgrade?

hi,
one thing today is good is , I have tried to restore version 9.3.6 DB backup to 9.5 and 11.14 but not 13.4

the restore command is ONLY do not works for 13 and funny thing is, once use the SAME restore command to restore to V 13.4:

D:\Program Files\PostgreSQL\13\bin>psql.exe -U postgres -p 5435 -f C:\Users\Administrator\Downloads\Alfresco_13.sql

it change the postgreSQL admin account password as well, and I can't login using DB tools anymore JUST AFTER I restore using the command above!

 I do the backup using

D:\Program Files\PostgreSQL\13\bin\pg_dumpall -U postgres >Alfresco_13.sql Alfresco_13.sql

Select allOpen in new window

and this backup the data from 9.3.6 instance which online.

I can do the restore to v 11.14 but not V13.4, any reason for it?

how can I check the password I set when I am installing postgreSQL ?  any text file store this information?

and I also try to use the SAME file I successfully restore to 11.14 to restore to 13.4, still the same thing! the restore change the postgreSQL password again ! What's wrong with PostgreSQL 13.4?

and before I do the restore for 9.5 and 11.14 I didn't do this:

1. Copy over your  pg_hba.conf file and transfer all configs/settings from the existing version  postgresql.conf  to new postgres.conf file from v.11

this is not a MUST right?


can this command:

D:\Program Files\PostgreSQL\13\bin\pg_dumpall -U postgres >Alfresco_13.sql 

Select allOpen in new window

specify the Port information so that it is not backup something else? I am worrying about I backed up the wrong thing, but it should not be, as restore to V 9.5 and 11.14 is ok.

today what I tried is, as backup using pg_dumpall is not going to work from 11.14 to 13.4, how about pg_upgrade:

"D:\Program Files\PostgreSQL\13\bin\pg_upgrade.exe" --old-bind
ir="D:\Program Files\PostgreSQL\11\bin" --new-bindir="D:\Program Files\PostgreSQ
L\13\bin" --old-datadir="D:\Program Files\PostgreSQL\11\data" --new-datadir="D:\
Program Files\PostgreSQL\13\data" --old-port=5434 --new-port=5435 -c -k -U postgres

Select allOpen in new window

what I got is :

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok


connection to database failed: fe_sendauth: no password supplied


could not connect to source postmaster started with the command:
"D:/Program Files/PostgreSQL/11/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "D:
/Program Files/PostgreSQL/11/data" -o "-p 5434 -b " start
Failure, exiting

Select allOpen in new window

it seems looking for password to login, what parameter I have to pass to pg_upgrade so that it knows the password of the postgreSQL ?

and I found this URL:
https://www.enterprisedb.com/docs/epas/12/epas_upgrade_guide/03_upgrading_an_installation_with_pg_upgrade/05_pg_upgrade_troubleshooting/

Upgrade Error - fe_sendauth: no password supplied

If pg_upgrade reports an authentication error that references a missing password, please modify the pg_hba.conf files in the old and new cluster to enable trust authentication, or configure the system to use a pgpass.conf file.

and I follow this one:
https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html

to change to trust mode and this works now :

"D:\Program Files\PostgreSQL\13\bin\pg_upgrade.exe" --old-bind
ir="D:\Program Files\PostgreSQL\11\bin" --new-bindir="D:\Program Files\PostgreSQ
L\13\bin" --old-datadir="D:\Program Files\PostgreSQL\11\data" --new-datadir="D:\
Program Files\PostgreSQL\13\data" -U postgres --old-port=5434 --new-port=5435 -k

but after that old  cluster 11.14 can't start anymore, and the log from the pg_upgrade :



I have remove the .old from the file and old cluster can be start ! but now the new cluster can start up but CAN'T login !!!

any idea? any log information ?

the error on screen is just

FATAL: password authentication failed for user "postgres"

Hi,
Prior executing the pg_upgrade command in a command window then set the PGPASS environment variable to the Postgres user password.

SET PGPASSWORD=....

Select allOpen in new window

Regards,
    Tomas Helgi

tks.

I use trust method and it is ok ,how about thsi:


but after that old  cluster 11.14 can't start anymore, and the log from the pg_upgrade :




I have remove the .old from the file and old cluster can be start ! but now the new cluster can start up but CAN'T login !!!
but after that old  cluster 11.14 can't start anymore, and the log from the pg_upgrade :



I have remove the .old from the file and old cluster can be start ! but now the new cluster can start up but CAN'T login !!!

I have remove the .old from the file and old cluster can be start ! but now the new cluster can start up but CAN'T login !!!

any idea? any log information ?

I find out the log and 13.4 log say the same thing, password error! why only after pg_upgrade is done ?
and if I change pg_hba.conf:

# "local" is for Unix domain socket connections only
# local   all             all                                     scram-sha-256
local   all             all                                     trust
# IPv4 local connections:
# host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             127.0.0.1/32            trust

Select allOpen in new window

use trust mode instead of scram-sha-256, then I can login back on !

scram-sha-256 is the one work BEFORE pg_upgrade ! this is amazing that why always 13.4 have problem AFTER import script from pg_dumpall and pg_upgrade.

what method allow password input ? 13.4 back to md5?

and this really works:

# "local" is for Unix domain socket connections only
# local   all             all                                     scram-sha-256
local   all             all                                     md5
# local   all             all                                     trust
# IPv4 local connections:
# host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             127.0.0.1/32            md5
# host    all             all             127.0.0.1/32            trust

Select allOpen in new window

hi
I am now thinking of enhancing the speed of pg_dump and I read the doc, it seems ONLY --jobs=N can helps:

pg_dump -U postgres -p 5432 --jobs=8 > backup.sql

it returns errors :

pg_dump: parallel backup only supported by the directory format

Select allOpen in new window

why is it ?
--jobs=N seems can't work for pg_dumpall, right ? so pg_dumpall can't optimized?

and I do this:

pg_dump -U postgres -p 5432 -Fc -j 8  > backup.sql

pg_dump: parallel backup only supported by the directory format

Select allOpen in new window

I can't see why.

pg_upgrade however, can use --jobs=N ?

Hi,
This command

pg_dump -U postgres -p 5432 --jobs=8 > backup.sql

Select allOpen in new window

will never work as you are running 8 jobs into one file. And that is why you are getting this error.
To use multiple backup jobs like this your backup command should look like this

pg_dump -U postgres -p 5432 -Fd --jobs=8 --file=<backup directorypath>

Select allOpen in new window

In another words. The backup process in this case creates 8 threads that run simultaneously and parallel where each thread creates a file in the backup directory until all tables in the database are backed up (one in each file).

Regards,
    Tomas Helgi

but we can't optimize the pg_dumpall by this way, right? only pg_dump can use many threads ?

only pg_dumpall can backup every thing than pg_dump right?

pg_dump -U postgres -p 5432 -Fd --jobs=8 --file=<backup directorypath>

you are saying this command will create 8 files in the path : backup directorypath ? then how can I restore it  using psql.exe ?

e.g. 
?

and how I also tried to run the pg_upgrade after setting :
SET PGPASSWORD=....

from 9.3.6 to 9.5 using

D:\PostgreSQL_tem>"D:\Program Files\PostgreSQL\9.5\bin\pg_upgrade.exe" --old-bin
dir="D:\Alfresco\postgresql\bin" --new-bindir="D:\Program Files\PostgreSQL\9.5\b
in" --old-datadir="D:/Alfresco/alf_data/postgresql" --new-datadir="D:\Program Fi
les\PostgreSQL\9.5\data" -U postgres --old-port=5432 --new-port=5433 -c -k

output is 
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user
database user "postgres" is not the install user
Failure, exiting

what is this error means? and how to solve that? the windows login user I am running must be also "postgres" ?

Hi,
The error

Checking database user is the install user
database user "postgres" is not the install user
Failure, exiting 

tells you everything what is wrong. ;)
Check what user is the owner of the cluster and process/service and you have the user that needs to be used to do the upgrade.

Regards,
    Tomas Helgi

tells you everything what is wrong. ;)

seems yes for me too but is not making any sense,

Check what user is the owner of the cluster and process/service

the postgreSQL I am upgrading from is a talent made PostgreSQL 9.3.6  for an application called Alfresco and the folder structure of it different from the official postgreSQL, e.g.Alfresco postgreSQL has this as the bin directory path:

D:\Alfresco\postgresql\bin

Select allOpen in new window

The official one is :

D:\Program Files\PostgreSQL\<version>\bin


when I try the pg_upgrade between 2 x official postgreSQL installation my command:

"D:\Program Files\PostgreSQL\9.5\bin\pg_upgrade.exe" --old-bin
dir="D:\Alfresco\postgresql\bin" --new-bindir="D:\Program Files\PostgreSQL\9.5\b
in" --old-datadir="D:/Alfresco/alf_data/postgresql" --new-datadir="D:\Program Fi
les\PostgreSQL\9.5\data" -U postgres --old-port=5432 --new-port=5433 -c -k

Select allOpen in new window

work perfectly! but not today as I tried to do the same thing on a modifyed PostgreSQL, I am not sure why.

Check what user is the owner of the cluster  

how to check? the one I use to install postgreSQL is the owner of the cluster?
and it must have the windows account with the same name, use it to login and do the upgrade?

Tomas,

of course, all the result her is based on UAT/test, we are not in PROD yet.

lcohan ,

"and you should have said that from the start."

Sorry, I ONLY know if when I actually try it using modified postgreSQL and official postgreSQL.

when I try between office postgreSQL it is working fine ! but when I try using none offical one (I just think all postgreSQL installation is the SAME, but it seems it is not now, application vendor can modify it!)

I don't think I am and for this type of upgrade the best in my opinion would be for you to migrate/upgrade your user databases but taking a full backup from current server and restore into the new Postgres 11.14 server however

at this moment, it seems only pg_dumpall is working fine with it and it seems pg_dumpall by offical postgreSQL v13 is much faster than any version before that.

BTW,

Copy over your  pg_hba.conf file and transfer all configs/settings from the existing version  postgresql.conf  to new postgres.conf file from v.11

I know why now, specially if these 2 x files has been modified before....

tks both, Hope I am not going to come back for the same thing!

we will do pilot upgrade soon as see if it helps

note: the support from postgreSQL community is wrong, they said 2 x community cant install on the same server with none default collation.

I can't do it but the default one seems COPY the same collation as the installed one! 

right now if we have only 1 x DB need to migration and we can only use pg_dumpall, the backup time is 3 hours and 8 hours of restore.

anyway to make it much faster?

pg_dumpall with compressoin:

http://www.whiteboardcoder.com/2012/05/postgres-pgdumpall-with-compression.html

only work for psql on linux, right ? we use psql on Windows /

hi all,

We are trying to shorten the backup and restore time by :

1) backup compression

     pg_dump -U postgres -p 5432 -Fd --jobs=8 -Fc <target DB name>  --file=<backup directorypath> 

2) restore by using 10 CPU threads:

pg_restore  -U postgres -p 5434 -j 10 --dbname=<target DB name> --verbose D:\PostgreSQLbackup\xxx.sql >  D:\PostgreSQLbackup\result.txt

for 1) , it has error:

pg_dump: too many command-line arguments (first is "--file=D:\PostgreSQL_backup"
)
Try "pg_dump --help" for more information.

any idea of this ?

Hi,
Your last comment should be a new question as this is a closed solution.

Regards,
    Tomas Helgi

hi Tomas  

This is the reason

https://www.experts-exchange.com/questions/29238279/PostgreSQL-11-14-1-parallel-backup-error.html