Re: Column type modification in big tables

On Thu, 15 Aug, 2024, 9:18 pm Alban Hertroys, wrote: > > > On 15 Aug 2024, at 14:15, Lok P wrote: > > (…) > > > Hello Greg, > > > > In terms of testing on sample data and extrapolating, as i picked the > avg partition sizeof the table (which is ~20GB) and i created a non > partitioned table with

How to validate restore of backup?

Hi, I have some questions When doing pg_restore of backup of a database to a NEW server. Is there a way to ensure the data integrity is in tact, and user ID and access works liked how it was in the old server? How to properly handle the materialized views when backing up and restoring? Thanks.

Re: How to validate restore of backup?

Hi Vince, For validation of databases, you can use the following approach /usr/pgsql-16/bin/pg_dump -d postgres -h localhost -p 5428 | md5sum > /var/lib/pgsql/db1.txt /usr/pgsql-16/bin/pg_dump -d postgres -h localhost -p 5420 | md5sum > /var/lib/pgsql/db2.txt diff db1.txt db2.txt By executing abo

Re: How to validate restore of backup?

On Thu, Aug 22, 2024 at 7:06 AM Vince McMahon wrote: > Hi, > > I have some questions When doing pg_restore of backup of a database to a > NEW server. > > Is there a way to ensure the data integrity is in tact, and user ID and > access works liked how it was in the old server? > pg_restore is jus

Re: How to validate restore of backup?

That's great on small databases. Not so practical when they're big. On Thu, Aug 22, 2024 at 7:10 AM Muhammad Usman Khan wrote: > Hi Vince, > For validation of databases, you can use the following approach > > /usr/pgsql-16/bin/pg_dump -d postgres -h localhost -p 5428 | md5sum > > /var/lib/pgsql

Re: How to validate restore of backup?

On Thu, Aug 22, 2024 at 6:24 AM Ron Johnson wrote: > That's great on small databases. Not so practical when they're big. > > So - - - - what is the recommended procedure for 'large' databases? (Might be useful to have a definition for what a large database is as well.) Regards

Re: How to validate restore of backup?

On Thu, Aug 22, 2024 at 8:49 AM o1bigtenor wrote: > > > On Thu, Aug 22, 2024 at 6:24 AM Ron Johnson > wrote: > >> That's great on small databases. Not so practical when they're big. >> >> So - - - - what is the recommended procedure for 'large' databases? > > (Might be useful to have a definiti

Re: How to validate restore of backup?

On Thu, Aug 22, 2024 at 8:03 AM Ron Johnson wrote: > On Thu, Aug 22, 2024 at 8:49 AM o1bigtenor wrote: > >> >> >> On Thu, Aug 22, 2024 at 6:24 AM Ron Johnson >> wrote: >> >>> That's great on small databases. Not so practical when they're big. >>> >>> So - - - - what is the recommended procedur

Re: How to validate restore of backup?

On Thu, Aug 22, 2024 at 8:49 AM o1bigtenor wrote: > > > On Thu, Aug 22, 2024 at 6:24 AM Ron Johnson > wrote: > >> That's great on small databases. Not so practical when they're big. >> >> So - - - - what is the recommended procedure for 'large' databases? > Use a real backup system like pgBack

Re: How to validate restore of backup?

On Thu, Aug 22, 2024 at 10:22 AM Greg Sabino Mullane wrote: > On Thu, Aug 22, 2024 at 8:49 AM o1bigtenor wrote: > >> >> >> On Thu, Aug 22, 2024 at 6:24 AM Ron Johnson >> wrote: >> >>> That's great on small databases. Not so practical when they're big. >>> >>> So - - - - what is the recommended

Re: How to validate restore of backup?

On Thu, Aug 22, 2024 at 9:59 AM o1bigtenor wrote: > On Thu, Aug 22, 2024 at 8:03 AM Ron Johnson > wrote: > >> On Thu, Aug 22, 2024 at 8:49 AM o1bigtenor wrote: >> >>> On Thu, Aug 22, 2024 at 6:24 AM Ron Johnson >>> wrote: >>> That's great on small databases. Not so practical when they're

Re: How to validate restore of backup?

On 8/22/24 04:06, Vince McMahon wrote: Hi, I have some questions When doing pg_restore of backup of a database to a NEW server. How large a backup? Is there a way to ensure the data integrity is in tact, and user ID and access works liked how it was in the old server? As to user access,

Re: Planet Postgres and the curse of AI

On Tue, Aug 20, 2024 at 8:33 AM Greg Sabino Mullane wrote: > > On Tue, Jul 23, 2024 at 12:45 PM Avinash Vallarapu > wrote: >> >> However, I do agree with Lawrence that it is impossible to prove whether it >> is written by AI or a human. >> AI can make mistakes and it might mistakenly point out

Re: How to validate restore of backup?

Hi, Adrian. The largest one is 8 GB after compression. I have a window of 8 hours to handle 30 GB total of backup at various sizes. On Thu, Aug 22, 2024, 11:36 AM Adrian Klaver wrote: > On 8/22/24 04:06, Vince McMahon wrote: > > Hi, > > > > I have some questions When doing pg_restore of back

Re: Planet Postgres and the curse of AI

> Posts should be technically and factually correct agreed and period. no need qualify how the nonsense was created. -john On Thu, Aug 22, 2024 at 4:13 PM Robert Treat wrote: > > On Tue, Aug 20, 2024 at 8:33 AM Greg Sabino Mullane > wrote: > > > > On Tue, Jul 23, 2024 at 12:45 PM Avinash Val

Is there a way to translate pg_amop.amopstrategy into a description?

I'm digging into GiST indexes again, and ran into a helpful script here: https://medium.com/postgres-professional/indexes-in-postgresql-5-gist-86e19781b5db (This piece has shown up in many places in various versions.) I've adapted the search a little, as I'd like to make it easier to explore avai

Re: Is there a way to translate pg_amop.amopstrategy into a description?

Morris de Oryx writes: > What I'm hoping for is a function like > get_opt_class_strategy_description(optclass, straregy_number) I've > looked at the source a bit, and it seems that there is no such > function, and that it might well be difficult to implement. The > strategy numbers are, as far as

Re: How to validate restore of backup?

On 8/22/24 14:31, Vince McMahon wrote: Hi, Adrian. The largest one is 8 GB after compression. I have a window of 8 hours to handle 30 GB total of backup at various sizes. I assume by compression you mean using some form of pg_dump -Fc. As to your timeline determining whether that can be met

where is postres installed?

After installing PostgreSQL on my Debian-12 machine, I typed 'postgres --version' and got this msg: *bash: postgres: command not found* 'psql --version', however, does work and gives me this message : *psql (PostgreSQL) 16.3 (Debian 16.3-1.pgdg120+1)* Obviously postgres is not in the path, but

Where is my app installed?

After installing PostgreSQL on my Debian-12 machine, I typed 'postgres --version' and got this msg: *bash: postgres: command not found* 'psql --version', however, does work and gives me this message : *psql (PostgreSQL) 16.3 (Debian 16.3-1.pgdg120+1)* Obviously postgres is not in the path, but

Re: Where is my app installed?

On 8/22/24 17:36, Arbol One wrote: After installing PostgreSQL on my Debian-12 machine, I typed 'postgres --version' and got this msg: *bash: postgres: command not found* 'psql --version', however, does work and gives me this message : *psql (PostgreSQL) 16.3 (Debian 16.3-1.pgdg120+1)* Obviou

Re: Where is my app installed?

On Thu, 2024-08-22 at 20:36 -0400, Arbol One wrote: >   > After installing PostgreSQL on my Debian-12 machine, I typed > 'postgres --version' and got this msg: >  bash: postgres: command not found >   > 'psql --version', however, does work and gives me this message : >   > psql (PostgreSQL) 16.3 (D

Re: Where is my app installed?

Adrian Klaver writes: > On 8/22/24 17:36, Arbol One wrote: >> After installing PostgreSQL on my Debian-12 machine, I typed 'postgres >> --version' and got this msg: >> *bash: postgres: command not found* >> 'psql --version', however, does work and gives me this message : >> *psql (PostgreSQL) 16.

Re: Where is my app installed?

On 8/22/24 19:21, Tom Lane wrote: Adrian Klaver writes: On 8/22/24 17:36, Arbol One wrote: After installing PostgreSQL on my Debian-12 machine, I typed 'postgres --version' and got this msg: *bash: postgres: command not found* 'psql --version', however, does work and gives me this message : *p

Re: where is postres installed?

Hi Arbol, You can try from the following commands: dpkg-query -L postgresql-16 which psql sudo find / -name "postgres" 2>/dev/null On Fri, 23 Aug 2024 at 05:35, Arbol One wrote: > After installing PostgreSQL on my Debian-12 machine, I typed 'postgres > --version' and got this msg: > *bash: pos

Re: where is postres installed?

Hi Arbol, Hope above response from Usman must have resolved your issue. You may also try by finding any binary of PostgreSQL. e.g. find /usr -name pg_ctl /usr/local/pgsql/bin/pg_ctl /usr/lib/postgresql/16/bin/pg_ctl Regards, Ikram On Fri, Aug 23, 2024 at 9:33 AM Muhammad Usman Khan wrote:

Re: Where is my app installed?

Hi Arbol, Try to find any binary e.g. find /usr -name pg_ctl /usr/local/pgsql/bin/pg_ctl /usr/lib/postgresql/16/bin/pg_ctl find /usr -name psql /usr/bin/psql /usr/local/pgsql/bin/psql /usr/lib/postgresql/16/bin/psql Later you may create a symlink or add in PATH. Hope this helps. Regards, I

Re: Where is my app installed?

On 8/22/24 21:57, Muhammad Ikram wrote: Hi Arbol, Try to find any binary  e.g. find /usr -name pg_ctl /usr/local/pgsql/bin/pg_ctl /usr/lib/postgresql/16/bin/pg_ctl  find /usr -name psql /usr/bin/psql /usr/local/pgsql/bin/psql /usr/lib/postgresql/16/bin/psql Later you may create a symlink or

Re: where is postres installed?

On Thu, Aug 22, 2024 at 8:36 PM Arbol One wrote: > After installing PostgreSQL on my Debian-12 machine, I typed 'postgres > --version' and got this msg: > *bash: postgres: command not found* > > 'psql --version', however, does work and gives me this message : > > *psql (PostgreSQL) 16.3 (Debian 1

Re: where is postres installed?

Hi Arbol Use this command and it will show all the files installed by the package. dpkg-query -L postgresql-16 Regards Kashif Zeeshan On Fri, Aug 23, 2024 at 9:50 AM Muhammad Ikram wrote: > Hi Arbol, > > Hope above response from Usman must have resolved your issue. You may also > try by fin

Re: How to validate restore of backup?

On 2024-08-22 16:09:47 +0500, Muhammad Usman Khan wrote: > For validation of databases, you can use the following approach > > /usr/pgsql-16/bin/pg_dump -d postgres -h localhost -p 5428 | md5sum > > /var/lib/ > pgsql/db1.txt > /usr/pgsql-16/bin/pg_dump -d postgres -h localhost -p 5420 | md5sum >