Re: PG 9.1 much slower than 8.2 ?

This is not what I'm doing here: I stoped PostgreSQL 9.1, uninstalled it and removed it's data folder at /var/lib/postgresql/9.1 completely.
Now it is completely empty.

Also, just trying to run "/usr/lib/postgresql/9.3/bin/pg_ctl" without any parameters normally prints a usage overview like the following:

root@prod-cl3:/etc/postgresql# /usr/lib/postgresql/9.1/bin/pg_ctl
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
root@prod-cl3:/etc/postgresql#

The data stor isn't involved in this yet. instead I get this "symbol lookup error: undefined symbol: PQping".

I tried to purge all packages from the PGDG Repo and tried to reinstall 9.1 from Debian repo. Now I get the same issue with these Packages, too.

*sigh* ... I'm taking the "Restart from scratch" - road now, focusing in the initial performance-issue this time :/

Am 27.08.2014 17:24, schrieb Keith:

You cannot use your old 9.1 cluster with 9.3. You either have to redo
your dump and restore using a newly initialized cluster or perform a
pg_upgrade on the 9.1 cluster. I'd recommend going with the dump and
restore since it's much easier for those new to Postgres.


On Wed, Aug 27, 2014 at 11:18 AM, Marc Richter <mail@marc-richter.info

<mailto:mail@marc-richter.info>> wrote:

    Hey Keith,

    thanks for pointing me to this. I have removed the Debian postgres
    9.1 packages, inserted the repo of PGDG and installed PostgreSQL 9.3
    packages from there.

    Now, what I get is this:

    root@prod-cl4:/etc/postgresql/9.3/main# /etc/init.d/postgresql start
    [....] Starting PostgreSQL 9.3 database server: main[....] Error:
    could not exec /usr/lib/postgresql/9.3/bin/pg_ctl
    /usr/lib/postgresql/9.3/bin/pg_ctl start -D
    /var/lib/postgresql/9.3/main -s -o -c
    config_fi[FAILetc/postgresql/9.3/main/postgresql.conf" : ... failed!
      failed!
    root@prod-cl4:/etc/postgresql/9.3/main#
    /usr/lib/postgresql/9.3/bin/pg_ctl
    /usr/lib/postgresql/9.3/bin/pg_ctl: symbol lookup error:
    /usr/lib/postgresql/9.3/bin/pg_ctl: undefined symbol: PQping
    root@prod-cl4:/etc/postgresql/9.3/main#

    I cannot find something what seems related using Google ... what am
    I doing wrong here?

    Best regards,
    Marc

    Am 26.08.2014 17 <tel:26.08.2014%2017>:42, schrieb Keith:

        On Tue, Aug 26, 2014 at 11:10 AM, Marc Richter
        <mail@marc-richter.info <mailto:mail@marc-richter.info>

        <mailto:mail@marc-richter.info

        <mailto:mail@marc-richter.info>__>> wrote:

             Hi everyone,

             I'm in the process of migrating a really old PostgreSQL DB from
             8.2.5 to a (more) recent PostgreSQL 9.1. I know that 9.1 is
        somewhat
             old already, too, but since we are stuck to Debian stable
        and don't
             want to start using self-compiled software and this is the
        version
             which is included in Debian stable currently, this is the
        version of
             choice.

             I've managed to create a dump of the database from 8.2.5 and
             inserting it into 9.1.13 successfully, thanks to the help
        of this
             list ("Upgrading from PG 8.2.5 to 9.1.13" - Thread). So I
        gave the
             result to another department to make their compatibility- and
             overall-tests on it.
             They did not come up with incompatibilities, but with a
             performance-related issue:

             When we do a "SELECT *" on a table with 355332 rows in it
        without
             using an index or limit or such, this takes round about
        10.5 seconds
             on the PostgreSQL 8.2.5 host and 12.2 seconds on the PostgreSQL
             9.1.13 host. Both servers are using the same database.

             I know, this seems like near to nothing, but the hardware
        of the
             9.1.13 host is way more recent than the one of the 8.2.5
        PostgreSQL,
             too:

             PG Version 8.2.5:
             * CPU:  Intel Xeon CPU E5506 (4-Core 2,13 GHz)
             * RAM:  4 GB (2x2GB) DDR3 1066
             * Storage:
             System, SWAP und PostgreSQL Data:       RAID1 - ST3500320NS

             PG Version 9.1.13:
             * CPU:  AMD Opteron 4334 (6 Core 3,1 GHz)
             * RAM:  32 GB (4x8GB) DDR3 1600
             * Storage:
             System + SWAP:          RAID1 - ST1000DM003-1CH1
             PostgreSQL Data:        RAID1 - SD6SB1M2 (SSD)

             I know that PostgreSQL has little chance to optimize a
        query like
             this, when no logic and no index is used to lookup a
        result, but
             taking this into account, we would have expected that
        issuing the
             same, bad query on old hardware and newer hardware once, should
             deliver results on the better/newer hardware a lot faster
        than on
             the older one. Instead, we experience the opposite.

             Are we missing a "OMG - how can you even start a postgres
        without
             doing .... first???" step here? What else can be the reason
        for this?

             These are the postgres.conf - files in use:

              >>>>>>> PostgreSQL 8.2.5:

             listen_addresses = '*'
             max_connections = 100
             shared_buffers = 24MB
             max_fsm_pages = 153600
             datestyle = 'iso, dmy'
             lc_messages = 'de_DE.UTF-8'
             lc_monetary = 'de_DE.UTF-8'
             lc_numeric = 'de_DE.UTF-8'
             lc_time = 'de_DE.UTF-8'

              >>>>>>> PostgreSQL 9.1.13:

             data_directory = '/var/lib/postgresql/9.1/main'

             hba_file = '/etc/postgresql/9.1/main/pg_____hba.conf'
             ident_file = '/etc/postgresql/9.1/main/pg_____ident.conf'
             external_pid_file = '/var/run/postgresql/9.1-main.____pid'

             listen_addresses = '*'
             port = 5432
             max_connections = 512
             unix_socket_directory = '/var/run/postgresql'
             ssl = true
             shared_buffers = 2048MB
             temp_buffers = 8MB
             work_mem = 256MB
             maintenance_work_mem = 1GB
             checkpoint_segments = 16
             effective_cache_size = 24GB
             log_destination = 'syslog'
             syslog_facility = 'LOCAL0'
             syslog_ident = 'postgres'
             client_min_messages = warning
             log_min_messages = notice
             log_min_error_statement = info
             log_line_prefix = '%m %r %u '
             log_statement = 'mod'
             datestyle = 'iso, mdy'
             lc_messages = 'de_DE.UTF-8'
             lc_monetary = 'de_DE.UTF-8'
             lc_numeric = 'de_DE.UTF-8'
             lc_time = 'de_DE.UTF-8'
             default_text_search_config = 'pg_catalog.english'

             Thanks for reading and your help in advance.

             Best regards,
             Marc


             --
             Sent via pgsql-novice mailing list
        (pgsql-novice@postgresql.org <mailto:pgsql-novice@postgresql.org>

             <mailto:pgsql-novice@__postgresql.org

        <mailto:pgsql-novice@postgresql.org>>)

             To make changes to your subscription:

        http://www.postgresql.org/____mailpref/pgsql-novice
        <http://www.postgresql.org/__mailpref/pgsql-novice>

             <http://www.postgresql.org/__mailpref/pgsql-novice
        <http://www.postgresql.org/mailpref/pgsql-novice>>



        I don't have a comment on the query performance at this time,
        but I just
        wanted to point out that there is an apt repository maintained
        by the
        PostgreSQL Global Development Group for debian based distros that
        contains more recent packages of postgres

        https://wiki.postgresql.org/__wiki/Apt
        <https://wiki.postgresql.org/wiki/Apt>



    --

    Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org
    <mailto:pgsql-novice@postgresql.org>)
    To make changes to your subscription:
    http://www.postgresql.org/__mailpref/pgsql-novice
    <http://www.postgresql.org/mailpref/pgsql-novice>

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice