Results 1 to 8 of 8

Thread: PostgreSQL tcp/ip connection error

  1. #1

    Default PostgreSQL tcp/ip connection error

    After upgrading to OpenSuse 13.2, I cannot connect to the server

    Code:
     psql -U         postgres -h 192.168.1.36
          psql: could not connect to server: Connection refused
              Is the server running on host "192.168.1.36" and         accepting
              TCP/IP connections on port 5432?
    
    However, in pg_hba.conf I have
    Code:
    # TYPE  DATABASE        USER                    ADDRESS                 METHOD
          
          # "local" is for Unix domain socket connections only
          local   all                     all                                     trust
          # IPv4 local connections:
          host    all             all                     127.0.0.1/32            trust
          # IPv6 local connections:
          host    all             all                     ::1/128                 trust
          # Allow replication connections from localhost, by a user         with the
          # replication privilege.
          #local   replication             postgres                                trust
          #host    replication     postgres                127.0.0.1/32            trust
          #host    replication     postgres                ::1/128                 trust
          host    all             all                     192.168.1.36/24         trust
    and in postgresql.conf I have

    Code:
    listen_addresses = '*'                  # what IP         address(es) to listen on;
                                                  # comma-separated         list of addresses;
                                                  # defaults to         'localhost'; use '*' for all
                                                  # (change         requires restart)
          port = 5432                             # (change         requires restart)
          max_connections = 100                   # (change         requires restart)
    Before the upgrade this was working. Did I miss something?

    Ivan

  2. #2

    Default Re: PostgreSQL tcp/ip connection error

    Anything in the db.*.log files upon startup? What does the following show
    from the PostgreSQL server to show if PostgreSQL is actually bound for
    listening?

    Code:
    sudo /usr/sbin/ss -planeto | grep 5432
    sudo /usr/sbin/ss -planeto | grep postgres
    I would expect something like this:

    Code:
    LISTEN     0      128               0.0.0.0:5432                     *:*
    users:(("postgres",26401,4)) uid:108 ino:12611619 sk:f06b6140ffff8801
    LISTEN     0      128                     :::5432                    :::*
    users:(("postgres",26401,3)) uid:108 ino:12611618 sk:f1c157c0ffff8801
    If so, then the next step is to run your psql command on the server. If
    that works, verify the firewall is not blocking access:

    Code:
    sudo /usr/sbin/iptables -nvL | grep 5432

    --
    Good luck.

    If you find this post helpful and are logged into the web interface,
    show your appreciation and click on the star below...

  3. #3

    Default Re: PostgreSQL tcp/ip connection error

    The real problem is that by upgrading to opensuse 13.2, the system also upgraded to PostgreSQL version 9.3. And now, I cannot open my 9.2 data files.

    Can anybody help me and point out how I can migrate those data files? I cannot use pg_dump because PostgreSQL is not running.

    Code:
    systemctl status postgresql
    postgresql.service - LSB: Start the PostgreSQL master daemon
       Loaded: loaded (/etc/init.d/postgresql)
       Active: active (exited) since Mon 2014-12-29 13:23:23 CET; 2h 4min ago
      Process: 41039 ExecStop=/etc/init.d/postgresql stop (code=exited, status=0/SUCCESS)
      Process: 41054 ExecStart=/etc/init.d/postgresql start (code=exited, status=5)
    
    Dec 29 13:23:23 virtserver postgresql[41054]: Your database files were created by PostgreSQL version 9.2.
    Dec 29 13:23:23 virtserver postgresql[41054]: Could not find executables for this version.
    Dec 29 13:23:23 virtserver postgresql[41054]: Please install the PostgreSQL server package for version 9.2.

  4. #4

    Default Re: PostgreSQL tcp/ip connection error

    Quote Originally Posted by eulaersivan View Post
    Can anybody help me and point out how I can migrate those data files? I cannot use pg_dump because PostgreSQL is not running.
    PostgreSQL 9.2 is available here, even for 13.2:
    http://software.opensuse.org/package/postgresql92
    Click on "Show unstable packages", which should not worry you. "Unstable" in this context just means that they are not officially included in the corresponding openSUSE release. You should use the server:database:postgresql repo, which is kind of "official", as it's the development project for openSUSE Factory. They keep maintaining the older versions as well though.

    AIUI, those packages are co-installable with 9.3.
    I cannot help you further though, as I never used PostgreSQL myself, and know nothing about it (other than that it's an SQL database server)...
    Last edited by wolfi323; 29-Dec-2014 at 07:53.

  5. #5

    Default Re: PostgreSQL tcp/ip connection error

    Have you tried pg_upgrade? If you can get the 9.2 binaries on your system
    (anywhere perhaps, even from a .tar.gz maybe) then pg_upgrade sounds useful:

    http://www.postgresql.org/docs/9.2/s...pgupgrade.html


    --
    Good luck.

    If you find this post helpful and are logged into the web interface,
    show your appreciation and click on the star below...

  6. #6
    Join Date
    Oct 2011
    Location
    Germany (Ore Mountains)
    Posts
    428

    Default Re: PostgreSQL tcp/ip connection error

    You could use a tool named "pg_upgrade". The package postgresql93-contrib contains it.

    See:
    http://www.postgresql.org/docs/9.3/s...pgupgrade.html

    Hendrik

  7. #7

    Default Re: PostgreSQL tcp/ip connection error

    I've also installed the server edition of postgresql 9.2. I'm almost there ...

    Code:
    pg_upgrade -b /DBA/postgresql/9.2.7/bin/ -B /usr/lib/postgresql93/bin/ -d /var/lib/pgsql/data92/ -D /var/lib/pgsql/data93/ 
    Performing Consistency Checks
    -----------------------------
    Checking cluster versions                                   ok
    Checking database user is a superuser                       ok
    Checking for prepared transactions                          ok
    Checking for reg* system OID user data types                ok
    Checking for contrib/isn with bigint-passing mismatch       ok
    Creating dump of global objects                             ok
    Creating dump of database schemas
                                                                ok
    
    lc_collate cluster values do not match:  old "C", new "en_GB.UTF-8"
    Failure, exiting

  8. #8

    Default Re: PostgreSQL tcp/ip connection error

    Did you solve the problem eulaersivan? I got the same problem when I upgrade to opensuse 13.2 and follow this to solve without using the pg_upgrade.
    https://dfspspirit.wordpress.com/201...l-version-9-1/

    It works fine. Of course, it's a new empty database and I didn't need any other database with 9.2 version.

Posting Permissions

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