pg_upgrade issue from postgres 9.3 to 9.6












0















I am trying to use pg_upgrade to upgrade postgres server from 9.3 to 9.6.
I have both binaries on the windows server R8.



Here are the 2 pg_hba.conf :



> 9.3 :
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
#local all all 127.0.0.1/32 TRUST
host all all 127.0.0.1/32 TRUST
host all all 10.0.0.0/8 md5

9.6 :
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
#local all all 127.0.0.1/32 TRUST
host all all 127.0.0.1/32 TRUST
host all all 10.0.0.0/8 md5



  • I have all permissions on the directories.

  • From 9.6 bin I execute :
    pg_upgrade.exe path_of_oldbin path_of_newbin path_of_olddata path_of_newdata


All I got is :




connection to database failed : could not connect to server : connection refusedpg_upgrade fails




I tried unsuccessfully some different config (local) as you see in pg_hba.conf, but can't find the reason why it can't connect. Any idea ?










share|improve this question
















bumped to the homepage by Community 14 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • This error comes from before pg_hba.conf is even reached. Are you sure the DB accepting connections on that port?

    – dezso
    Apr 18 '18 at 14:18











  • How can I test that?

    – Leehan
    Apr 18 '18 at 14:39











  • Try to start it up using the command shown in the error and then try to connect. What is listen_addresses in postgresql.conf?

    – dezso
    Apr 18 '18 at 14:46






  • 1





    "TRUE" is not a legal authentication method, so the server would not even start up given those pg_hba.conf files. If you followed the hints and looked at the last few lines of the server startup log file, it probably would have indicated this. Are you looking for "trust" rather than "TRUE"?

    – jjanes
    Apr 18 '18 at 19:07











  • @deszo In postgres.conf : listen_adresses = '*', port = 5432 ; You are right jjanes, I don't know why I used TRUE rather than TRUST. Anw I changed in first post. But the error message is still the same. And in log : it says that TRUST is not a valid method. (I forget : both servers are off)

    – Leehan
    Apr 23 '18 at 7:04


















0















I am trying to use pg_upgrade to upgrade postgres server from 9.3 to 9.6.
I have both binaries on the windows server R8.



Here are the 2 pg_hba.conf :



> 9.3 :
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
#local all all 127.0.0.1/32 TRUST
host all all 127.0.0.1/32 TRUST
host all all 10.0.0.0/8 md5

9.6 :
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
#local all all 127.0.0.1/32 TRUST
host all all 127.0.0.1/32 TRUST
host all all 10.0.0.0/8 md5



  • I have all permissions on the directories.

  • From 9.6 bin I execute :
    pg_upgrade.exe path_of_oldbin path_of_newbin path_of_olddata path_of_newdata


All I got is :




connection to database failed : could not connect to server : connection refusedpg_upgrade fails




I tried unsuccessfully some different config (local) as you see in pg_hba.conf, but can't find the reason why it can't connect. Any idea ?










share|improve this question
















bumped to the homepage by Community 14 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • This error comes from before pg_hba.conf is even reached. Are you sure the DB accepting connections on that port?

    – dezso
    Apr 18 '18 at 14:18











  • How can I test that?

    – Leehan
    Apr 18 '18 at 14:39











  • Try to start it up using the command shown in the error and then try to connect. What is listen_addresses in postgresql.conf?

    – dezso
    Apr 18 '18 at 14:46






  • 1





    "TRUE" is not a legal authentication method, so the server would not even start up given those pg_hba.conf files. If you followed the hints and looked at the last few lines of the server startup log file, it probably would have indicated this. Are you looking for "trust" rather than "TRUE"?

    – jjanes
    Apr 18 '18 at 19:07











  • @deszo In postgres.conf : listen_adresses = '*', port = 5432 ; You are right jjanes, I don't know why I used TRUE rather than TRUST. Anw I changed in first post. But the error message is still the same. And in log : it says that TRUST is not a valid method. (I forget : both servers are off)

    – Leehan
    Apr 23 '18 at 7:04
















0












0








0








I am trying to use pg_upgrade to upgrade postgres server from 9.3 to 9.6.
I have both binaries on the windows server R8.



Here are the 2 pg_hba.conf :



> 9.3 :
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
#local all all 127.0.0.1/32 TRUST
host all all 127.0.0.1/32 TRUST
host all all 10.0.0.0/8 md5

9.6 :
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
#local all all 127.0.0.1/32 TRUST
host all all 127.0.0.1/32 TRUST
host all all 10.0.0.0/8 md5



  • I have all permissions on the directories.

  • From 9.6 bin I execute :
    pg_upgrade.exe path_of_oldbin path_of_newbin path_of_olddata path_of_newdata


All I got is :




connection to database failed : could not connect to server : connection refusedpg_upgrade fails




I tried unsuccessfully some different config (local) as you see in pg_hba.conf, but can't find the reason why it can't connect. Any idea ?










share|improve this question
















I am trying to use pg_upgrade to upgrade postgres server from 9.3 to 9.6.
I have both binaries on the windows server R8.



Here are the 2 pg_hba.conf :



> 9.3 :
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
#local all all 127.0.0.1/32 TRUST
host all all 127.0.0.1/32 TRUST
host all all 10.0.0.0/8 md5

9.6 :
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
#local all all 127.0.0.1/32 TRUST
host all all 127.0.0.1/32 TRUST
host all all 10.0.0.0/8 md5



  • I have all permissions on the directories.

  • From 9.6 bin I execute :
    pg_upgrade.exe path_of_oldbin path_of_newbin path_of_olddata path_of_newdata


All I got is :




connection to database failed : could not connect to server : connection refusedpg_upgrade fails




I tried unsuccessfully some different config (local) as you see in pg_hba.conf, but can't find the reason why it can't connect. Any idea ?







postgresql upgrade






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Apr 23 '18 at 7:03







Leehan

















asked Apr 18 '18 at 14:06









LeehanLeehan

1177




1177





bumped to the homepage by Community 14 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 14 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.















  • This error comes from before pg_hba.conf is even reached. Are you sure the DB accepting connections on that port?

    – dezso
    Apr 18 '18 at 14:18











  • How can I test that?

    – Leehan
    Apr 18 '18 at 14:39











  • Try to start it up using the command shown in the error and then try to connect. What is listen_addresses in postgresql.conf?

    – dezso
    Apr 18 '18 at 14:46






  • 1





    "TRUE" is not a legal authentication method, so the server would not even start up given those pg_hba.conf files. If you followed the hints and looked at the last few lines of the server startup log file, it probably would have indicated this. Are you looking for "trust" rather than "TRUE"?

    – jjanes
    Apr 18 '18 at 19:07











  • @deszo In postgres.conf : listen_adresses = '*', port = 5432 ; You are right jjanes, I don't know why I used TRUE rather than TRUST. Anw I changed in first post. But the error message is still the same. And in log : it says that TRUST is not a valid method. (I forget : both servers are off)

    – Leehan
    Apr 23 '18 at 7:04





















  • This error comes from before pg_hba.conf is even reached. Are you sure the DB accepting connections on that port?

    – dezso
    Apr 18 '18 at 14:18











  • How can I test that?

    – Leehan
    Apr 18 '18 at 14:39











  • Try to start it up using the command shown in the error and then try to connect. What is listen_addresses in postgresql.conf?

    – dezso
    Apr 18 '18 at 14:46






  • 1





    "TRUE" is not a legal authentication method, so the server would not even start up given those pg_hba.conf files. If you followed the hints and looked at the last few lines of the server startup log file, it probably would have indicated this. Are you looking for "trust" rather than "TRUE"?

    – jjanes
    Apr 18 '18 at 19:07











  • @deszo In postgres.conf : listen_adresses = '*', port = 5432 ; You are right jjanes, I don't know why I used TRUE rather than TRUST. Anw I changed in first post. But the error message is still the same. And in log : it says that TRUST is not a valid method. (I forget : both servers are off)

    – Leehan
    Apr 23 '18 at 7:04



















This error comes from before pg_hba.conf is even reached. Are you sure the DB accepting connections on that port?

– dezso
Apr 18 '18 at 14:18





This error comes from before pg_hba.conf is even reached. Are you sure the DB accepting connections on that port?

– dezso
Apr 18 '18 at 14:18













How can I test that?

– Leehan
Apr 18 '18 at 14:39





How can I test that?

– Leehan
Apr 18 '18 at 14:39













Try to start it up using the command shown in the error and then try to connect. What is listen_addresses in postgresql.conf?

– dezso
Apr 18 '18 at 14:46





Try to start it up using the command shown in the error and then try to connect. What is listen_addresses in postgresql.conf?

– dezso
Apr 18 '18 at 14:46




1




1





"TRUE" is not a legal authentication method, so the server would not even start up given those pg_hba.conf files. If you followed the hints and looked at the last few lines of the server startup log file, it probably would have indicated this. Are you looking for "trust" rather than "TRUE"?

– jjanes
Apr 18 '18 at 19:07





"TRUE" is not a legal authentication method, so the server would not even start up given those pg_hba.conf files. If you followed the hints and looked at the last few lines of the server startup log file, it probably would have indicated this. Are you looking for "trust" rather than "TRUE"?

– jjanes
Apr 18 '18 at 19:07













@deszo In postgres.conf : listen_adresses = '*', port = 5432 ; You are right jjanes, I don't know why I used TRUE rather than TRUST. Anw I changed in first post. But the error message is still the same. And in log : it says that TRUST is not a valid method. (I forget : both servers are off)

– Leehan
Apr 23 '18 at 7:04







@deszo In postgres.conf : listen_adresses = '*', port = 5432 ; You are right jjanes, I don't know why I used TRUE rather than TRUST. Anw I changed in first post. But the error message is still the same. And in log : it says that TRUST is not a valid method. (I forget : both servers are off)

– Leehan
Apr 23 '18 at 7:04












1 Answer
1






active

oldest

votes


















0














I just went through this same issue myself, trying to upgrade from 9.4 to 9.6.



I am running PostgreSQL in Windows 10.



I received the same error message.



I was confused about running cmd.exe under user: postgres. My system doesn't have an account with that name.



I did two things.





  1. pg_hba.conf file were modified for both versions to add the "method" "trust". I did not add extra lines as you might have found in other references. However, comparing my entries with the original poster, I had the default localhost entries as per what they added. I would comment md5 lines temporarily:





    pg_hba.conf





    I duplicated and commented out the lines you see with : md5 under IPv4 and IPv6




  2. Running the pg_upgrade command might be affected if you run cmd.exe as admin or as a regular user. Extra consideration may be needed to ensure write privileges on affected folders (check folder security if you get the same error) when running pg_upgrade command.



    In my case, my setup has the PostgreSQL application files stored in C:program files directory and data is stored in C:APPSPostgreSQLversiondata



    Referencing my setup, I ran the following command:





    pg_upgrade command





    -c option is to check clusters - I consider it a test. You would run without this option once your ready to migrate.



    -U postgres - this was added after I updated the pg_hba.conf file correctly and not seeing the same error but instead a new one:





    results from pg_upgrade command





    The -U option allows you to specify a different postgreSQL user account for the command to use. This is where I specified the user to be postgres. Maybe a correction is required with PostgreSQL documentation to perform RUNAS /USER:postgres CMD.EXE and instead mention to use -U option




This is the result I received after running modified command:





successfully running pg_upgrade using -U option





Hope this helps.






share|improve this answer

























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f204330%2fpg-upgrade-issue-from-postgres-9-3-to-9-6%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    I just went through this same issue myself, trying to upgrade from 9.4 to 9.6.



    I am running PostgreSQL in Windows 10.



    I received the same error message.



    I was confused about running cmd.exe under user: postgres. My system doesn't have an account with that name.



    I did two things.





    1. pg_hba.conf file were modified for both versions to add the "method" "trust". I did not add extra lines as you might have found in other references. However, comparing my entries with the original poster, I had the default localhost entries as per what they added. I would comment md5 lines temporarily:





      pg_hba.conf





      I duplicated and commented out the lines you see with : md5 under IPv4 and IPv6




    2. Running the pg_upgrade command might be affected if you run cmd.exe as admin or as a regular user. Extra consideration may be needed to ensure write privileges on affected folders (check folder security if you get the same error) when running pg_upgrade command.



      In my case, my setup has the PostgreSQL application files stored in C:program files directory and data is stored in C:APPSPostgreSQLversiondata



      Referencing my setup, I ran the following command:





      pg_upgrade command





      -c option is to check clusters - I consider it a test. You would run without this option once your ready to migrate.



      -U postgres - this was added after I updated the pg_hba.conf file correctly and not seeing the same error but instead a new one:





      results from pg_upgrade command





      The -U option allows you to specify a different postgreSQL user account for the command to use. This is where I specified the user to be postgres. Maybe a correction is required with PostgreSQL documentation to perform RUNAS /USER:postgres CMD.EXE and instead mention to use -U option




    This is the result I received after running modified command:





    successfully running pg_upgrade using -U option





    Hope this helps.






    share|improve this answer






























      0














      I just went through this same issue myself, trying to upgrade from 9.4 to 9.6.



      I am running PostgreSQL in Windows 10.



      I received the same error message.



      I was confused about running cmd.exe under user: postgres. My system doesn't have an account with that name.



      I did two things.





      1. pg_hba.conf file were modified for both versions to add the "method" "trust". I did not add extra lines as you might have found in other references. However, comparing my entries with the original poster, I had the default localhost entries as per what they added. I would comment md5 lines temporarily:





        pg_hba.conf





        I duplicated and commented out the lines you see with : md5 under IPv4 and IPv6




      2. Running the pg_upgrade command might be affected if you run cmd.exe as admin or as a regular user. Extra consideration may be needed to ensure write privileges on affected folders (check folder security if you get the same error) when running pg_upgrade command.



        In my case, my setup has the PostgreSQL application files stored in C:program files directory and data is stored in C:APPSPostgreSQLversiondata



        Referencing my setup, I ran the following command:





        pg_upgrade command





        -c option is to check clusters - I consider it a test. You would run without this option once your ready to migrate.



        -U postgres - this was added after I updated the pg_hba.conf file correctly and not seeing the same error but instead a new one:





        results from pg_upgrade command





        The -U option allows you to specify a different postgreSQL user account for the command to use. This is where I specified the user to be postgres. Maybe a correction is required with PostgreSQL documentation to perform RUNAS /USER:postgres CMD.EXE and instead mention to use -U option




      This is the result I received after running modified command:





      successfully running pg_upgrade using -U option





      Hope this helps.






      share|improve this answer




























        0












        0








        0







        I just went through this same issue myself, trying to upgrade from 9.4 to 9.6.



        I am running PostgreSQL in Windows 10.



        I received the same error message.



        I was confused about running cmd.exe under user: postgres. My system doesn't have an account with that name.



        I did two things.





        1. pg_hba.conf file were modified for both versions to add the "method" "trust". I did not add extra lines as you might have found in other references. However, comparing my entries with the original poster, I had the default localhost entries as per what they added. I would comment md5 lines temporarily:





          pg_hba.conf





          I duplicated and commented out the lines you see with : md5 under IPv4 and IPv6




        2. Running the pg_upgrade command might be affected if you run cmd.exe as admin or as a regular user. Extra consideration may be needed to ensure write privileges on affected folders (check folder security if you get the same error) when running pg_upgrade command.



          In my case, my setup has the PostgreSQL application files stored in C:program files directory and data is stored in C:APPSPostgreSQLversiondata



          Referencing my setup, I ran the following command:





          pg_upgrade command





          -c option is to check clusters - I consider it a test. You would run without this option once your ready to migrate.



          -U postgres - this was added after I updated the pg_hba.conf file correctly and not seeing the same error but instead a new one:





          results from pg_upgrade command





          The -U option allows you to specify a different postgreSQL user account for the command to use. This is where I specified the user to be postgres. Maybe a correction is required with PostgreSQL documentation to perform RUNAS /USER:postgres CMD.EXE and instead mention to use -U option




        This is the result I received after running modified command:





        successfully running pg_upgrade using -U option





        Hope this helps.






        share|improve this answer















        I just went through this same issue myself, trying to upgrade from 9.4 to 9.6.



        I am running PostgreSQL in Windows 10.



        I received the same error message.



        I was confused about running cmd.exe under user: postgres. My system doesn't have an account with that name.



        I did two things.





        1. pg_hba.conf file were modified for both versions to add the "method" "trust". I did not add extra lines as you might have found in other references. However, comparing my entries with the original poster, I had the default localhost entries as per what they added. I would comment md5 lines temporarily:





          pg_hba.conf





          I duplicated and commented out the lines you see with : md5 under IPv4 and IPv6




        2. Running the pg_upgrade command might be affected if you run cmd.exe as admin or as a regular user. Extra consideration may be needed to ensure write privileges on affected folders (check folder security if you get the same error) when running pg_upgrade command.



          In my case, my setup has the PostgreSQL application files stored in C:program files directory and data is stored in C:APPSPostgreSQLversiondata



          Referencing my setup, I ran the following command:





          pg_upgrade command





          -c option is to check clusters - I consider it a test. You would run without this option once your ready to migrate.



          -U postgres - this was added after I updated the pg_hba.conf file correctly and not seeing the same error but instead a new one:





          results from pg_upgrade command





          The -U option allows you to specify a different postgreSQL user account for the command to use. This is where I specified the user to be postgres. Maybe a correction is required with PostgreSQL documentation to perform RUNAS /USER:postgres CMD.EXE and instead mention to use -U option




        This is the result I received after running modified command:





        successfully running pg_upgrade using -U option





        Hope this helps.







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Jan 4 at 20:28

























        answered Oct 17 '18 at 17:52









        LocoLeoLocoLeo

        11




        11






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f204330%2fpg-upgrade-issue-from-postgres-9-3-to-9-6%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh