Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2017
    Posts
    8
    Provided Answers: 1

    Unanswered: Upgrading from psql 9.1 to 9.6

    I am looking to upgrade psql v9.1 to 9.6 on an Ubuntu 14.04 machine.

    I have a single user dbuser and database dbname, which need to be retained/converted from current to future databases

    Some step by step instructions would be valuable to me, as I am familiar with databases in general, but new to psql

  2. #2
    Join Date
    Jun 2017
    Posts
    8
    Provided Answers: 1

    Question Can I recover a database without the root db password?

    OK, so I have a dump of a database, taken by using that dbs username, but I cannot log into the db using elevation; su - postgres
    This would make everything simpler however, so if there is a way that would be valuable.

    The situation is;
    psql9.1 was installed,
    I dumped the db I wanted using
    pg_dump -f /psql/backup -h localhost -p [port] -U user dbname

    I then removed psql 9.1 and installed 9.4 and it was painstaking!

    But that is done now, so now I have a 9.4 server which I cannot log into.
    I am looking for a way to set psql 9.4 to defaults so that I can create my own users and generate my own root passwords.

    Please let me know, this has become quite critical for me.

    Many thanks

  3. #3
    Join Date
    Nov 2003
    Posts
    2,982
    Provided Answers: 23
    What is the exact error message you get when you try to restore the dump?

    Can you connect using psql?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    Join Date
    Jun 2017
    Posts
    8
    Provided Answers: 1
    Hi Shammat thank-you so much again,

    The error I get is
    user@server:/psql$ psql -U user database < /location/file -W
    Password for user user:
    psql: FATAL: Peer authentication failed for user "user"
    user@server:/psql$

    I am unclear what credentials to use, as I had removed psql9.1 and installed psql9.5 and had, perhaps wrongly, predicted that my database and userbase would now be clean.

    Regardless of this however, I am unable to import a database with psql -U user database < /location/file -W and inputting my previous accounts password.

    I can of course restore the server and database, but this access control is beginning to frustrate me because I can't determine if the old users are still being used, even after uninstalling psql9.1 and installing psql9.5 or not.

    When I have tried to log into the database;
    root@server:/etc/postgresql# psql dbname -U user
    psql: FATAL: Peer authentication failed for user "user"
    root@server:/etc/postgresql# psql dbname -U user -W
    Password for user user:
    psql: FATAL: Peer authentication failed for user "user"


    As for your request:
    root@server:/etc/postgresql# psql -U user dbname < /psql/backup -W
    Password for user user:
    psql: FATAL: Peer authentication failed for user "user"
    root@server:/etc/postgresql#

  5. #5
    Join Date
    Nov 2003
    Posts
    2,982
    Provided Answers: 23
    psql: FATAL: Peer authentication failed for user "user"
    You probably want to configure "trust" or "md5" authentication for that user:

    https://www.postgresql.org/docs/curr...-hba-conf.html
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •