Re: Data migration from postgres 8.4 to 9.4
On Mon, Apr 16, 2018 at 12:33 PM, Akshay Ballarpure < akshay.ballarp...@tcs.com> wrote: > 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: akshay.ballarp...@tcs.com > Website: http://www.tcs.com You cannot simply copy data between major versions. Look into pg_upgrade utility to upgrade your database, or you could use pg_dump/pg_restore to migrate between major versions. Amitabh
Re: Is PostgreSQL SQL Database Command Syntax Similar to MySQL/MariaDB?
On Tue, Mar 31, 2020 at 5:58 PM Turritopsis Dohrnii Teo En Ming < c...@teo-en-ming.com> wrote: > Does phpMyAdmin support PostgreSQL? > > > > On 2020-03-31 20:15, Ben Madin wrote: > > and \q works in MySQL, so it may not be different anyway :) > > > > On Tue, 31 Mar 2020 at 08:18, Tom Lane wrote: > > > >> Eric MacAdie writes: > >>> One thing that is different about Postgres is that it uses a lot > >> of > >>> "meta-commands". > >>> https://www.postgresql.org/docs/current/app-psql.html > >>> An important one is "\q", which exits you from the database. > >> Typing > >>> "quit" or "exit" won't get you out of the session. > >> > >> Actually, since v11 that does work ... a concession we've made > >> to MySQL converts ;-) > >> > >> regards, tom lane > > > > -- > > > > [1] > > > > Dr Ben Madin > > BVMS MVPHMgmt PhD MANZCVS GAICD > > Managing Director > > > > Mobile: > > +61 448 887 220 [2] > > > > E-mail: > > b...@ausvet.com.au > > > > Website: > > www.ausvet.com.au [1] > > Skype: benmadin > > > > Address: > > 5 Shuffrey Street > > Fremantle, WA 6160 > > Australia > > > > Links: > > -- > > [1] https://www.ausvet.com.au/ > > [2] tel:+61448887220 > > > -BEGIN EMAIL SIGNATURE- > > The Gospel for all Targeted Individuals (TIs): > > [The New York Times] Microwave Weapons Are Prime Suspect in Ills of > U.S. Embassy Workers > > Link: > https://www.nytimes.com/2018/09/01/science/sonic-attack-cuba-microwave.html > > > > > Singaporean Mr. Turritopsis Dohrnii Teo En Ming's Academic > Qualifications as at 14 Feb 2019 and refugee seeking attempts at the > United Nations Refugee Agency Bangkok (21 Mar 2017), in Taiwan (5 Aug > 2019) and Australia (25 Dec 2019 to 9 Jan 2020): > > [1] https://tdtemcerts.wordpress.com/ > > [2] https://tdtemcerts.blogspot.sg/ > > [3] https://www.scribd.com/user/270125049/Teo-En-Ming > > -END EMAIL SIGNATURE- > > > If you are looking something similar, the closest that you can come is phpPgAdmin . Otherwise there is pgAdmin 4
Finding free time period on non-continous tstzrange field values
Hi
Given the following table, how do I find free time period.
CREATE TABLE test_time_range (
id SERIAL PRIMARY KEY,
time_range tstzrange);
Insert into test_time_range(time_range) values('[2022-11-28 08:00:00,
2022-11-28 20:00:00]');
Insert into test_time_range(time_range) values('[2022-11-29 12:30:00,
2022-11-29 22:00:00]');
Insert into test_time_range(time_range) values('[2022-11-30 05:00:00,
2022-11-30 19:00:00]');
In the above example, I would like the query to return something like this:
"2022-11-28 20:01:00 2022-11-29 11:29:00"
"2022-11-29 22:01:00 2022-11-30 04:59:00"
Apologies if this is a dumb question, but trying to use range for the first
time , and can't get my head around it.
Using PG14, can upgrade to 15 if that matters.
Amitabh
Re: Finding free time period on non-continous tstzrange field values
On Wed, Nov 30, 2022 at 7:20 PM Marcos Pegoraro wrote: > Given the following table, how do I find free time period. >> > > > https://www.crunchydata.com/blog/better-range-types-in-postgres-14-turning-100-lines-of-sql-into-3 > Thanks Marcos .. Had seen this earlier but somehow slipped my mind to use it for implementation. Will try implementing using tstzmultirange.
Re: Finding free time period on non-continous tstzrange field values
On Wed, Nov 30, 2022 at 7:13 PM Amitabh Kant wrote:
> Hi
>
> Given the following table, how do I find free time period.
>
> CREATE TABLE test_time_range (
> id SERIAL PRIMARY KEY,
> time_range tstzrange);
>
>
> Insert into test_time_range(time_range) values('[2022-11-28 08:00:00,
> 2022-11-28 20:00:00]');
> Insert into test_time_range(time_range) values('[2022-11-29 12:30:00,
> 2022-11-29 22:00:00]');
> Insert into test_time_range(time_range) values('[2022-11-30 05:00:00,
> 2022-11-30 19:00:00]');
>
>
> In the above example, I would like the query to return something like this:
> "2022-11-28 20:01:00 2022-11-29 11:29:00"
> "2022-11-29 22:01:00 2022-11-30 04:59:00"
>
>
> Apologies if this is a dumb question, but trying to use range for the
> first time , and can't get my head around it.
>
> Using PG14, can upgrade to 15 if that matters.
>
>
> Amitabh
>
Based on Marcos suggestions (
https://www.crunchydata.com/blog/better-range-types-in-postgres-14-turning-100-lines-of-sql-into-3
), I tried the following query :
SELECT tstzmultirange(tstzrange('2022-11-25 00:00:00', '2022-11-30
00:00:00', '[]')) -
range_agg(time_range) AS availability
FROM test_time_range
WHERE time_range && tstzrange('2022-11-25 00:00:00', '2022-11-30 00:00:00',
'[]');
but then I receive the following error. My guess is I need to cast the
tstzrange output, but can't seem to find the correct cast.
ERROR: function tstzmultirange(tstzrange) does not exist
LINE 1: SELECT tstzmultirange(tstzrange('2022-11-25 00:00:00', '2022... ^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts. SQL state: 42883 Character: 8
Re: Finding free time period on non-continous tstzrange field values
On Thu, Dec 1, 2022 at 7:59 PM Tom Lane wrote:
> Amitabh Kant writes:
> > I tried the following query :
>
> > SELECT tstzmultirange(tstzrange('2022-11-25 00:00:00', '2022-11-30
> > 00:00:00', '[]')) -
> > range_agg(time_range) AS availability
> > FROM test_time_range
> > WHERE time_range && tstzrange('2022-11-25 00:00:00', '2022-11-30
> 00:00:00',
> > '[]');
>
> > but then I receive the following error. My guess is I need to cast the
> > tstzrange output, but can't seem to find the correct cast.
>
> > ERROR: function tstzmultirange(tstzrange) does not exist
> > LINE 1: SELECT tstzmultirange(tstzrange('2022-11-25 00:00:00', '2022... ^
> > HINT: No function matches the given name and argument types. You might
> need
> > to add explicit type casts. SQL state: 42883 Character: 8
>
> That function certainly should exist:
>
> psql (14.6)
> Type "help" for help.
>
> postgres=# \df tstzmultirange
> List of functions
>Schema | Name | Result data type | Argument data types |
> Type
>
> ++--+--+--
> pg_catalog | tstzmultirange | tstzmultirange | |
> func
> pg_catalog | tstzmultirange | tstzmultirange | VARIADIC tstzrange[] |
> func
> pg_catalog | tstzmultirange | tstzmultirange | tstzrange|
> func
> (3 rows)
>
> My guess is that your server is not in fact PG14, but some
> older version.
>
> regards, tom lane
>
My apologies as indeed was the case. Server is running 13.6 Will
update the server and try it again.
Amitabh
Re: Purging few months old data and vacuuming in production
On Fri, Dec 30, 2022 at 12:09 PM Ranjith Paliyath wrote: > Hi, > > We have a PostgreSQL (slightly old version, something like - PostgreSQL > 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat > 4.8.5-36), 64-bit) production, where one particular table and its related 5 > tables need to be purged of 3 months prior data. Each of these tables' > daily record increment is on an average 2 to 3 million. > > Approach needed is to do a daily purge of 90days prior data. Probable > purge processing window is expected to be 2hrs. Observed test timing for > deletion is exceeding 2-3hrs and we are trying to do vacuuming after the > deletes, which is again taking exceeding another 2hrs. > There is a suggestion for re-creating the tables with partitions, and as > purge approach could then be a deletion/dropping of these partitions, which > would not really require a vacuuming later on. > > When we go for a Daily purge approach it should not put a strain on other > processes which could be affecting this same set of tables, like these > tables should not get locked because of the purge. > > Questions are - > (a) Should we recommend PostgreSQL upgrade, if possible, to v15.1? Could > this bring in some benefits related to vacuuming? > (b) Would partitioning be an optimal approach? > > Thank you, > Regards > > > This electronic mail (including any attachment thereto) may be > confidential and privileged and is intended only for the individual or > entity named above. Any unauthorized use, printing, copying, disclosure or > dissemination of this communication may be subject to legal restriction or > sanction. Accordingly, if you are not the intended recipient, please notify > the sender by replying to this email immediately and delete this email (and > any attachment thereto) from your computer system...Thank You. > > > Partitioning would definitely help, if you partition by date .. Also, if my memory serves me right, v 15 would definitely help if you have large number of partitions, which in your case it's most likely to be the case. Deleting from single table would be putting a strain on your existing table. Amitabh
Re: PgbackRest : Stanza creation fails on DB Server and Repo Server
On Wed, Aug 28, 2024 at 8:00 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, August 28, 2024, KK CHN wrote: > >> >> and I have .pgpass in DB server as >> > > You assumed this mattered but I see no mention that pgBackRest consults > this file. > > It seems to require the local entry in pg_hba.conf to use peer > authentication. > > David J. > > Section 21.4 on this page ( https://pgbackrest.org/user-guide.html#introduction ) does seem to mention the use of .pgpass file. I have no idea about the actual problem though. Amitabh
Re: timescaledb vs NULL vs pg_timeseries vs partman + pgcron + pg_ivm
On Wed, Apr 9, 2025 at 11:50 AM Achilleas Mantzios - cloud < a.mantz...@cloud.gatewaynet.com> wrote: > > On 4/9/25 04:50, Amitabh Kant wrote: > > Thank you, I meant the paid/supported service not the community version. >> Which of the two do you use? >> >> >> I use the community version. > > > On 4/9/25 05:23, Brent Wood wrote: > > I also use the free community edition on internal servers, but under > Ubuntu. No issues and very good performance. > > Brent Wood... > > > Thanks Amitabh abd Brent, how do you plan to cope with future upgrades > based on logical replication ? Do you run timescale on a dedicated/separate > system from the rest of your PostgreSQL cluster(s)? > I don't use the inbuilt logical replication. I depend upon pgBackRest for my backups. My database is mainly time series data, and runs on the same systems.
Re: timescaledb vs NULL vs pg_timeseries vs partman + pgcron + pg_ivm
On Tue, Apr 8, 2025 at 9:40 PM Achilleas Mantzios - cloud < a.mantz...@cloud.gatewaynet.com> wrote: > Hi > > timescaledb seemed mature, but also exotic, allow me the term. No way to > use native logical replication, shortage of options to run on premise or > self hosted, which leaves us with those options : > > > > I cannot comment on the applicability of timescaledb in your context, but running it on premise/self-hosted has posed no problems, at least on Debian. If I understood your query incorrectly, please ignore. Amitabh
Re: timescaledb vs NULL vs pg_timeseries vs partman + pgcron + pg_ivm
On Tue, Apr 8, 2025 at 11:29 PM Achilleas Mantzios < a.mantz...@cloud.gatewaynet.com> wrote: > > On 8/4/25 20:37, Amitabh Kant wrote: > > On Tue, Apr 8, 2025 at 9:40 PM Achilleas Mantzios - cloud < > a.mantz...@cloud.gatewaynet.com> wrote: > >> Hi >> >> timescaledb seemed mature, but also exotic, allow me the term. No way to >> use native logical replication, shortage of options to run on premise or >> self hosted, which leaves us with those options : >> >> >> >> > I cannot comment on the applicability of timescaledb in your context, but > running it on premise/self-hosted has posed no problems, at least on > Debian. > > If I understood your query incorrectly, please ignore. > > Thank you, I meant the paid/supported service not the community version. > Which of the two do you use? > > > I use the community version.
