Data migration from postgres 8.4 to 9.4
Hello, I need help in using postgresql 8.4 data in postgres 9.4 version. Do I need to run any tool to achieve the same? Steps i followed is ran postgresql 8.4 and 9.4, copied data from 8.4 instance to 9.4 and try to start postgresql 9.4 but no luck, getting below error. [root@ms-esmon esm-data]# su - postgres -c "/opt/rh/rh-postgresql94/root/usr/bin/postgres -D /var/ericsson/esm-data/postgresql-data/ 2>&1 &" [root@ms-esmon esm-data]# LOG: skipping missing configuration file "/var/ericsson/esm-data/postgresql-data/postgresql.auto.conf" 2018-04-16 06:52:01.546 GMT FATAL: database files are incompatible with server 2018-04-16 06:52:01.546 GMT DETAIL: The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.4.9. With Best Regards Akshay Ericsson OSS MON Tata Consultancy Services Mailto: [email protected] Website: http://www.tcs.com Experience certainty. IT Services Business Solutions Consulting =-=-= Notice: The information contained in this e-mail message and/or attachments to it may contain confidential or privileged information. If you are not the intended recipient, any dissemination, use, review, distribution, printing or copying of the information contained in this e-mail message and/or attachments to it are strictly prohibited. If you have received this communication in error, please notify us by reply e-mail or telephone and immediately and permanently delete the message and any attachments. Thank you
Re: Data migration from postgres 8.4 to 9.4
Am 16.04.2018 um 09:03 schrieb Akshay Ballarpure: > Hello, Hi Akshay, > I need help in using postgresql 8.4 data in postgres 9.4 version. Do I > need to run any tool to achieve the same? Yes. (-performance is probably the wrong place to ask though, please try -general or -admin next time) Please check the release notes before doing *any* upgrade, esp. when skipping 4 major releases. They (among other very important information) contain instructions how to upgrade: https://www.postgresql.org/docs/current/static/release-9-4.html#id-1.11.6.48.4 You'll probably end up doing a pg_upgrade run (which is linked from the above). > > Steps i followed is ran postgresql 8.4 and 9.4, copied data from 8.4 > instance to 9.4 and try to start postgresql 9.4 but no luck, getting > below error. > > [root@ms-esmon esm-data]# su - postgres -c > "/opt/rh/rh-postgresql94/root/usr/bin/postgres -D > /var/ericsson/esm-data/postgresql-data/ 2>&1 &" > [root@ms-esmon esm-data]# LOG: skipping missing configuration file > "/var/ericsson/esm-data/postgresql-data/postgresql.auto.conf" > 2018-04-16 06:52:01.546 GMT *FATAL*: database files are incompatible > with server > 2018-04-16 06:52:01.546 GMT *DETAIL*: The data directory was > initialized by PostgreSQL version 8.4, which is not compatible with this > version 9.4.9. That's exactly what's supposed to happen. The reasons are explained in the pg_upgrade documentation. BTW: Are you sure you want to go to 9.4? It is already rather outdated and will go out of support "soon" (given that you're running 8.4, I have to assume that your organisation requires quite some time to get an upgrade cycle through the red band jungle). Unless you have very good reasons not to, please consider going straight to 10, which will get you almost 5 years of community support. Best regards, -- Gunnar "Nick" Bluth DBA ELSTER Extern im Auftrag der Hays AG Tel: +49 911/991-4665 Mobil: +49 172/8853339
Unexplainable execution time difference between two test functions...one using IF (SELECT COUNT(*) FROM...) and the other using IF EXISTS (SELECT 1 FROM...)
*A description of what you are trying to achieve and what results you
expect.:*
My end goal was to test the execution time difference between using an
IF(SELECT COUNT(*)...) and an IF EXISTS() when no indexes were used and
when a string match was not found. My expectation was that my 2 functions
would behave fairly similarly, but they most certainly did not. Here are
the table, functions, test queries, and test query results I received, as
well as comments as I present the pieces and talk about the results from my
perspective.
This is the table and data that I used for my tests. A table with 1
million sequenced records. No indexing on any columns. I ran ANALYZE on
this table and a VACUUM on the entire database, just to be sure.
CREATE TABLE zz_noidx1 AS SELECT generate_series(0, 99) AS
int_distinct, 'Test'::text || generate_series(0, 99)::text AS
text_distinct;
These are the 2 functions that I ran my final tests with. My goal was to
determine which function would perform the fastest and my expectation was
that they would still be somewhat close in execution time comparison.
--Test Function #1
CREATE OR REPLACE FUNCTION zz_spx_ifcount_noidx(p_findme text)
RETURNS text
LANGUAGE 'plpgsql'
STABLE
AS $$
BEGIN
IF (SELECT COUNT(*) FROM zz_noidx1 WHERE LOWER(zz_noidx1.text_distinct) =
LOWER(p_findme)) > 0 THEN
RETURN 'Found';
ELSE
RETURN 'Not Found';
END IF;
END;
$$;
--Test Function #2
CREATE OR REPLACE FUNCTION zz_spx_ifexists_noidx(p_findme text)
RETURNS text
LANGUAGE 'plpgsql'
STABLE
AS $$
BEGIN
IF EXISTS (SELECT 1 FROM zz_noidx1 WHERE LOWER(zz_noidx1.text_distinct) =
LOWER(p_findme)) THEN
RETURN 'Found';
ELSE
RETURN 'Not Found';
END IF;
END;
$$;
The first thing I did was to run some baseline tests using the basic
queries inside of the IF() checks found in each of the functions to see how
the query planner handled them. I ran the following two queries.
EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM zz_noidx1 WHERE
LOWER(text_distinct) = LOWER('Test501');
EXPLAIN (ANALYZE, BUFFERS) SELECT 1 FROM zz_noidx1 WHERE
LOWER(text_distinct) = LOWER('Test501');
The execution time results and query plans for these two were very similar,
as expected. In the results I can see that 2 workers were employed for
each query plan.
--Results for the SELECT COUNT(*) query.
QUERY
PLAN
Finalize Aggregate (cost=12661.42..12661.43 rows=1 width=8) (actual
time=172.105..172.105 rows=1 loops=1)
Buffers: shared
read=1912
-> Gather (cost=12661.21..12661.42 rows=2 width=8) (actual
time=172.020..172.099 rows=3 loops=1)
Workers Planned:
2
Workers Launched:
2
Buffers: shared
read=1912
-> Partial Aggregate (cost=11661.21..11661.22 rows=1 width=8) (actual
time=155.123..155.123 rows=1 loops=3)
Buffers: shared
read=5406
-> Parallel Seq Scan on zz_noidx1 (cost=0.00..11656.00 rows=2083
width=0) (actual time=155.103..155.103 rows=0 loops=3)
Filter: (lower(text_distinct) =
'test501'::text)
Rows Removed by Filter:
33
Buffers: shared
read=5406
Planning time: 0.718
ms
Execution time: 187.601 ms
--Results for the SELECT 1 query.
QUERY
PLAN
Gather (cost=1000.00..13156.00 rows=5000 width=4) (actual
time=175.682..175.682 rows=0 loops=1)
Workers Planned:
2
Workers Launched:
2
Buffers: shared
read=2021
-> Parallel Seq Scan on zz_noidx1 (cost=0.00..11656.00 rows=2083
width=4) (actual time=159.769..159.769 rows=0 loops=3)
Filter: (lower(text_distinct) =
'test501'::text)
Rows Removed by Filter:
33
Buffers: shared
read=5406
Planning time: 0.874
ms
Execution time: 192.045 ms
After running these baseline tests and viewing the fairly similar results,
right or wrong, I expected my queries that tested the functions to behave
similarly. I started with the following query...
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM
zz_spx_ifcount_noidx('Test501');
and I got the following "auto_explain" results...
2018-04-16 14:57:22.624 EDT [17812] LOG: duration: 155.239 ms plan:
Query Text: SELECT (SELECT COUNT(*) FROM zz_noidx1 WHERE
LOWER(zz_noidx1.text_distinct) = LOWER(p_findme)) > 0
Partial Aggregate (cost=11661.21..11661.22 rows=1 width=8) (actual
time=155.230..155.230 rows=1 loops=1)
Buffers: shared read=1682
-> Parallel Seq Scan on zz_noidx1 (cost=0.00..11656.00 rows=2083
width=0) (actual time=155.222..155.222 rows=0 loops=1)
Filter: (lower(text_distinct) = 'test501'::text)
Rows Removed by Filter: 311170
Buffers: shared read=1682
2018-04-16 14:57:22.624 EDT [9096] LOG: duration: 154.603 ms plan:
Query Text: SELECT (SELECT COUNT(*) FROM zz_noidx1 WHERE
LOWER(zz_noidx1.text_distinct) = LOWER(p_findme)) > 0
Partial Aggregate (cost=11661.
Re: Unexplainable execution time difference between two test functions...one using IF (SELECT COUNT(*) FROM...) and the other using IF EXISTS (SELECT 1 FROM...)
Hi
2018-04-16 22:42 GMT+02:00 Hackety Man :
> *A description of what you are trying to achieve and what results you
> expect.:*
>
> My end goal was to test the execution time difference between using an
> IF(SELECT COUNT(*)...) and an IF EXISTS() when no indexes were used and
> when a string match was not found. My expectation was that my 2 functions
> would behave fairly similarly, but they most certainly did not. Here are
> the table, functions, test queries, and test query results I received, as
> well as comments as I present the pieces and talk about the results from my
> perspective.
>
> This is the table and data that I used for my tests. A table with 1
> million sequenced records. No indexing on any columns. I ran ANALYZE on
> this table and a VACUUM on the entire database, just to be sure.
>
> CREATE TABLE zz_noidx1 AS SELECT generate_series(0, 99) AS
> int_distinct, 'Test'::text || generate_series(0, 99)::text AS
> text_distinct;
>
> These are the 2 functions that I ran my final tests with. My goal was to
> determine which function would perform the fastest and my expectation was
> that they would still be somewhat close in execution time comparison.
>
> --Test Function #1
> CREATE OR REPLACE FUNCTION zz_spx_ifcount_noidx(p_findme text)
> RETURNS text
> LANGUAGE 'plpgsql'
> STABLE
> AS $$
>
> BEGIN
> IF (SELECT COUNT(*) FROM zz_noidx1 WHERE LOWER(zz_noidx1.text_distinct) =
> LOWER(p_findme)) > 0 THEN
> RETURN 'Found';
> ELSE
> RETURN 'Not Found';
> END IF;
> END;
> $$;
>
> --Test Function #2
> CREATE OR REPLACE FUNCTION zz_spx_ifexists_noidx(p_findme text)
> RETURNS text
> LANGUAGE 'plpgsql'
> STABLE
> AS $$
>
> BEGIN
> IF EXISTS (SELECT 1 FROM zz_noidx1 WHERE LOWER(zz_noidx1.text_distinct) =
> LOWER(p_findme)) THEN
> RETURN 'Found';
> ELSE
> RETURN 'Not Found';
> END IF;
> END;
> $$;
>
> The first thing I did was to run some baseline tests using the basic
> queries inside of the IF() checks found in each of the functions to see how
> the query planner handled them. I ran the following two queries.
>
> EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM zz_noidx1 WHERE
> LOWER(text_distinct) = LOWER('Test501');
> EXPLAIN (ANALYZE, BUFFERS) SELECT 1 FROM zz_noidx1 WHERE
> LOWER(text_distinct) = LOWER('Test501');
>
> The execution time results and query plans for these two were very
> similar, as expected. In the results I can see that 2 workers were
> employed for each query plan.
>
> --Results for the SELECT COUNT(*) query.
> QUERY PLAN
>
>
>
>
> Finalize Aggregate (cost=12661.42..12661.43 rows=1 width=8) (actual
> time=172.105..172.105 rows=1 loops=1)
> Buffers: shared read=1912
>
>
> -> Gather (cost=12661.21..12661.42 rows=2 width=8) (actual
> time=172.020..172.099 rows=3 loops=1)
> Workers Planned: 2
>
>
> Workers Launched: 2
>
>
> Buffers: shared read=1912
>
>
> -> Partial Aggregate (cost=11661.21..11661.22 rows=1 width=8) (actual
> time=155.123..155.123 rows=1 loops=3)
> Buffers: shared read=5406
>
>
> -> Parallel Seq Scan on zz_noidx1 (cost=0.00..11656.00 rows=2083
> width=0) (actual time=155.103..155.103 rows=0 loops=3)
> Filter: (lower(text_distinct) = 'test501'::text)
>
> Rows Removed by Filter: 33
>
> Buffers: shared read=5406
>
> Planning time: 0.718 ms
>
>
> Execution time: 187.601 ms
>
> --Results for the SELECT 1 query.
> QUERY PLAN
>
>
>
> Gather (cost=1000.00..13156.00 rows=5000 width=4) (actual
> time=175.682..175.682 rows=0 loops=1)
> Workers Planned: 2
>
>
> Workers Launched: 2
>
> Buffers: shared read=2021
>
>
> -> Parallel Seq Scan on zz_noidx1 (cost=0.00..11656.00 rows=2083
> width=4) (actual time=159.769..159.769 rows=0 loops=3)
> Filter: (lower(text_distinct) = 'test501'::text)
>
> Rows Removed by Filter: 33
>
> Buffers: shared read=5406
>
> Planning time: 0.874 ms
>
> Execution time: 192.045 ms
>
> After running these baseline tests and viewing the fairly similar results,
> right or wrong, I expected my queries that tested the functions to behave
> similarly. I started with the following query...
>
> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM zz_spx_ifcount_noidx('
> Test501');
>
> and I got the following "auto_explain" results...
>
> 2018-04-16 14:57:22.624 EDT [17812] LOG: duration: 155.239 ms plan:
> Query Text: SELECT (SELECT COUNT(*) FROM zz_noidx1 WHERE
> LOWER(zz_noidx1.text_distinct) = LOWER(p_findme)) > 0
> Partial Aggregate (cost=11661.21..11661.22 rows=1 width=8) (actual
> time=155.230..155.230 rows=1 loops=1)
>Buffers: shared read=1682
>-> Parallel Seq Scan on zz_noidx1 (cost=0.00..11656.00 rows=2083
> width=0) (actual time=155.222..155.222 rows=0 loops=1)
>Filt
