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 exactly same columns and populated with similar data > and also created same set of indexes on it and the underlying hardware is > exactly same as its on production. I am seeing it's taking ~5minutes to > alter all the four columns on this table. So we have ~90 partitions in > production with data in them and the other few are future partitions and > are blank. (Note- I executed the alter with "work_mem=4GB, > maintenance_work_mem=30gb, max_parallel_worker_per_gather=8, > max_parallel_maintenance_worker =16" ) > > > > So considering the above figures , can i safely assume it will take > ~90*5minutes= ~7.5hours in production and thus that many hours of downtime > needed for this alter OR do we need to consider any other factors or > activity here? > > Are all those partitions critical, or only a relative few? > > If that’s the case, you could: > 1) detach the non-critical partitions > 2) take the system down for maintenance > 3) update the critical partitions > 4) take the system up again > 5) update the non-critical partitions > 6) re-attach the non-critical partitions > > That could shave a significant amount of time off your down-time. I would > script the detach and re-attach processes first, to save some extra. > > Admittedly, I haven’t actually tried that procedure, but I see no reason > why it wouldn’t work. > > Apart perhaps, from inserts happening that should have gone to some of > those detached partitions. Maybe those could be sent to a ‘default’ > partition that gets detached at step 7, after which you can insert+select > those from the default into the appropriate partitions? > > But you were going to test that first anyway, obviously. > We were checking this strategy , but what we found is while attaching any of the historical partition back to the child table , if there runs any existing inserts on the other live partitions of the same child table that attach keeps on hang state. Also during this period the parent table (which is also partitioned) takes an exclusive lock on itself!! Even detaching any partition "concurrently" also waits for any inserts to finish, even those are on other partitions. Is this behavior expected?
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 above queries, if diff is null then it means there is no difference between source and destination databases. Adjust your port and databases accordingly. On Thu, 22 Aug 2024 at 16:06, 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? > > How to properly handle the materialized views when backing up and > restoring? > > Thanks. >
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 just a bunch of CREATE, COPY and ALTER statements, since pg_restore replays what was generated by pg_dump. There can be errors, of course. That's why I run "createdb $DB 2> /dev/null" to ensure that there's something for pg_restore to drop, then "pg_restore --create --clean --exit-on-error", and *most importantly*, check the return code!!! How to properly handle the materialized views when backing up and restoring? > > Thanks. > -- Death to America, and butter sauce. Iraq lobster!
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/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 above queries, if diff is null then it means there is no > difference between source and destination databases. Adjust your port and > databases accordingly. > > On Thu, 22 Aug 2024 at 16:06, 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? >> >> How to properly handle the materialized views when backing up and >> restoring? >> >> Thanks. >> > -- Death to America, and butter sauce. Iraq lobster!
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 definition for what a large database is as > well.) > "Large" is when it takes too long to run *TWO* text mode pg_dump commands *in addition to* the pg_dump and pg_restore. -- Death to America, and butter sauce. Iraq lobster!
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 procedure for 'large' databases? >> >> (Might be useful to have a definition for what a large database is as >> well.) >> > > "Large" is when it takes too long to run *TWO* text mode pg_dump commands *in > addition to* the pg_dump and pg_restore. > > Hm - - - - I'd say that's about as neat a non-answer as I've ever seen. Can you try again? (You forgot the first question - - - maybe you could try that one too - - - what is the recommended procedure for 'large' databases?) 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? > Use a real backup system like pgBackRest. Stop using pg_dump. Cheers, Greg
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 procedure for 'large' databases? >> > > Use a real backup system like pgBackRest. Stop using pg_dump. > Not useful when you're migrating not only between major versions but glibc levels. Use logical replication!! Maybe. It gets difficult with partitioned tables that regularly have children added and dropped; mistakes can be made. pg_dump/pg_restore is guaranteed to work. -- Death to America, and butter sauce. Iraq lobster!
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 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.) >>> >> >> "Large" is when it takes too long to run *TWO* text mode pg_dump >> commands *in addition to* the pg_dump and pg_restore. >> >> > Hm - - - - I'd say that's about as neat a non-answer as I've ever > seen. > Eh? If you've got hundreds of hours of down time to pipe a text-mode pg_dump of a TB-sized database through md5sum. twice, then that database isn't too big. I don't have that much down time; thus, it's "too big". Can you try again? > > (You forgot the first question - - - maybe you could try that one too - - > - what is the recommended procedure > for 'large' databases?) > I already did, in my message three hours ago. -- Death to America, and butter sauce. Iraq lobster!
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, write tests that cover that and run on the new cluster. Data is trickier and if that is possible to a degree of certainty is going to depend on answer to the first question above. How to properly handle the materialized views when backing up and restoring? create materialized view prj_mv(p_item_no, year) as select p_item_no, year from projection with data; pg_dump -d production -U postgres -h localhost -t projection -t prj_mv -f prj.sql In prj.sql: CREATE MATERIALIZED VIEW public.prj_mv AS SELECT p_item_no, year FROM public.projection WITH NO DATA; COPY public.projection ( ... [...] REFRESH MATERIALIZED VIEW public.prj_mv; It is done for you. Thanks. -- Adrian Klaver adrian.kla...@aklaver.com
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 that a blog is >> written by AI (which I know is difficult to implement). > > > Right - I am not interested in "proving" things, but I think a policy to > discourage overuse of AI is warranted. > >> People may also use AI generated Images in their blogs, and they may be >> meaningful for their article. >> Is it only the content or also the images ? It might get too complicated >> while implementing some rules. > > > Only the content, the images are perfectly fine. Even expected, these days. > >> >> Ultimately, Humans do make mistakes and we shouldn't discourage people >> assuming it is AI that made that mistake. > > > Humans make mistakes. AI confidently hallucinates. > I think this is a key point, and one that we could focus on for purposes of discouragement. Ie. "Blogs that are found to repeatedly post incorrect information and/or AI style hallucinations may be restricted from contributing to the planet postgres feed. This will be determined on a case by case basis." While it is likely impossible to come up with a set of rules that will satisfy some of the more legalistic folks among us, this would be a simple warning that would at least encourage folks to make sure they aren't posting bad information and leave a door open for enforcement if needed. And yes, this assumes that the folks running planet will enforce if needed, though I don't think it requires heavy policing at this point. Robert Treat https://xzilla.net
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 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, write tests that cover that and run on the new cluster. > > Data is trickier and if that is possible to a degree of certainty is > going to depend on answer to the first question above. > > > > > How to properly handle the materialized views when backing up and > restoring? > > create materialized view prj_mv(p_item_no, year) as select p_item_no, > year from projection with data; > > pg_dump -d production -U postgres -h localhost -t projection -t prj_mv > -f prj.sql > > In prj.sql: > > CREATE MATERIALIZED VIEW public.prj_mv AS > SELECT p_item_no, > year > FROM public.projection >WITH NO DATA; > > COPY public.projection ( ... > > > [...] > > REFRESH MATERIALIZED VIEW public.prj_mv; > > It is done for you. > > > > > Thanks. > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
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 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 that a blog is > >> written by AI (which I know is difficult to implement). > > > > > > Right - I am not interested in "proving" things, but I think a policy to > > discourage overuse of AI is warranted. > > > >> People may also use AI generated Images in their blogs, and they may be > >> meaningful for their article. > >> Is it only the content or also the images ? It might get too complicated > >> while implementing some rules. > > > > > > Only the content, the images are perfectly fine. Even expected, these days. > > > >> > >> Ultimately, Humans do make mistakes and we shouldn't discourage people > >> assuming it is AI that made that mistake. > > > > > > Humans make mistakes. AI confidently hallucinates. > > > > I think this is a key point, and one that we could focus on for > purposes of discouragement. Ie. "Blogs that are found to repeatedly > post incorrect information and/or AI style hallucinations may be > restricted from contributing to the planet postgres feed. This will be > determined on a case by case basis." While it is likely impossible to > come up with a set of rules that will satisfy some of the more > legalistic folks among us, this would be a simple warning that would > at least encourage folks to make sure they aren't posting bad > information and leave a door open for enforcement if needed. And yes, > this assumes that the folks running planet will enforce if needed, > though I don't think it requires heavy policing at this point. > > Robert Treat > https://xzilla.net > > -- Fast is fine, But accuracy is final. You must learn to be slow in a hurry. - Wyatt Earp
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 available index ops: SELECT amop.amopopr::regoperator AS operator, iif(amop.amoppurpose = 's', 'search','order') AS purpose, amop.amopstrategy AS stratgey_number -- I'd like to translate this into a description FROM pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop WHERE opc.opcname= 'gist_trgm_ops' AND am.amname = 'gist' AND opf.oid= opc.opcfamily AND am.oid = opf.opfmethod AND amop.amopfamily= opc.opcfamily AND amop.amoplefttype = opc.opcintype; +--+-+-+ | operator | purpose | stratgey_number | +--+-+-+ | %(text,text) | search | 1 | | <->(text,text) | order | 2 | | ~~(text,text)| search | 3 | | ~~*(text,text) | search | 4 | | ~(text,text) | search | 5 | | ~*(text,text)| search | 6 | | %>(text,text)| search | 7 | | <->>(text,text) | order | 8 | | %>>(text,text) | search | 9 | | <->>>(text,text) | order | 10 | | =(text,text) | search | 11 | +--+-+-+ 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 I can see, local to the specific opt_class, which has no requirement to label them in any particular way. Does anyone know if I'm missing something? Along the way, I did find that you can often look things up by hand in the source for specific tools, or review a lot of the strategies in one place: https://github.com/postgres/postgres/blob/edcb71258504ed22abba8cc7181d2bab3762e757/src/include/catalog/pg_amop.dat#L82 It's easier to use the docs at that point. No lives hang in the balance here, but I'm hoping to learn something. Thanks for any help or clarification.
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 I can see, local to the specific > opt_class, which has no requirement to label them in any particular > way. That's correct. For btree and hash, the meanings of the strategy numbers are determined by the index AM; but for (IIRC) all of our other index AMs they're determined by the individual opclass. So anything like this would have to be implemented by dedicated code in each opclass. Perhaps that's worth doing, but it'd be a fair amount of work. regards, tom lane
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 is going to depend on a more detailed explanation on your part of what you expect from the dump/restore process. -- Adrian Klaver adrian.kla...@aklaver.com
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 I don't know where the 'apt-get' installed it or why it did not add it to the path. Is there a way I can locate the installation directory? Thank in advance. -- */ArbolOne.ca/* Using Fire Fox and Thunderbird. ArbolOne is composed of students and volunteers dedicated to providing free services to charitable organizations. ArbolOne on Java Development is in progress [ í ]
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 I don't know where the 'apt-get' installed it or why it did not add it to the path. Is there a way I can locate the installation directory? Thank in advance. -- */ArbolOne.ca/* Using Fire Fox and Thunderbird. ArbolOne is composed of students and volunteers dedicated to providing free services to charitable organizations. ArbolOne on Java Development is in progress [ í ]
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)* Obviously postgres is not in the path, but I don't know where the 'apt-get' installed it or why it did not add it to the path. Is there a way I can locate the installation directory? As to where the postgres command is: ls -al /usr/lib/postgresql/16/bin/ [...] -rwxr-xr-x 1 root root 10338808 Aug 7 06:13 postgres* [...] Why do you want to use postgres directly? Thank in advance. -- */ArbolOne.ca/* Using Fire Fox and Thunderbird. ArbolOne is composed of students and volunteers dedicated to providing free services to charitable organizations. ArbolOne on Java Development is in progress [ í ] -- Adrian Klaver adrian.kla...@aklaver.com
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 (Debian 16.3-1.pgdg120+1) > > Obviously postgres is not in the path, but I don't know where the > 'apt-get' installed it or why it did not add it to the path. > > Is there a way I can locate the installation directory? > > Thank in advance. > > find / -name postgres or dpkg-query -L postgresql-16 | grep 'bin/postgres$' You wouldn't normally invoke the server from the command line, so it doesn't need to be in anyone's path.
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.3 (Debian 16.3-1.pgdg120+1)* >> Obviously postgres is not in the path, but I don't know where the >> 'apt-get' installed it or why it did not add it to the path. > As to where the postgres command is: > ls -al /usr/lib/postgresql/16/bin/ Theory 1: postgres is packaged in a "postgresql-server" package and the OP only installed the base (client-side) package. Theory 2: postgres is installed into some directory not in the OP's PATH, such as /usr/sbin. Since it's primarily used as a daemon, this'd be a reasonable thing for a packager to do. I'd bet a nickel on #1, though, because I've not seen too many packagers put postgres somewhere other than where they put psql. "Separate server package" is extremely common though. regards, tom lane
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 : *psql (PostgreSQL) 16.3 (Debian 16.3-1.pgdg120+1)* Obviously postgres is not in the path, but I don't know where the 'apt-get' installed it or why it did not add it to the path. As to where the postgres command is: ls -al /usr/lib/postgresql/16/bin/ Theory 1: postgres is packaged in a "postgresql-server" package and the OP only installed the base (client-side) package. Theory 2: postgres is installed into some directory not in the OP's PATH, such as /usr/sbin. Since it's primarily used as a daemon, this'd be a reasonable thing for a packager to do. Yes in: ls -al /usr/lib/postgresql/16/bin/ I'd bet a nickel on #1, though, because I've not seen too many packagers put postgres somewhere other than where they put psql. "Separate server package" is extremely common though. Correct. The missing part is that in Debian/Ubuntu packaging when you use psql you are actually doing: ls -al /usr/bin/psql lrwxrwxrwx 1 root root 37 Aug 8 07:37 /usr/bin/psql -> ../share/postgresql-common/pg_wrapper The Debian packaging routes most things through pg_wrapper/postgresql-common a Perl script that does the magic of finding the correct binaries for each Postgres version. regards, tom lane -- Adrian Klaver adrian.kla...@aklaver.com
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: 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 I don't know where the > 'apt-get' installed it or why it did not add it to the path. > > Is there a way I can locate the installation directory? > > Thank in advance. > -- > *ArbolOne.ca* Using Fire Fox and Thunderbird. ArbolOne is composed of > students and volunteers dedicated to providing free services to charitable > organizations. ArbolOne on Java Development is in progress [ í ] >
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: > 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: 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 I don't know where the >> 'apt-get' installed it or why it did not add it to the path. >> >> Is there a way I can locate the installation directory? >> >> Thank in advance. >> -- >> *ArbolOne.ca* Using Fire Fox and Thunderbird. ArbolOne is composed of >> students and volunteers dedicated to providing free services to charitable >> organizations. ArbolOne on Java Development is in progress [ í ] >> > -- Muhammad Ikram
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, Ikram On Fri, Aug 23, 2024 at 7:43 AM Adrian Klaver wrote: > 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 : > >>> *psql (PostgreSQL) 16.3 (Debian 16.3-1.pgdg120+1)* > >>> Obviously postgres is not in the path, but I don't know where the > >>> 'apt-get' installed it or why it did not add it to the path. > > > >> As to where the postgres command is: > >> ls -al /usr/lib/postgresql/16/bin/ > > > > Theory 1: postgres is packaged in a "postgresql-server" package > > and the OP only installed the base (client-side) package. > > > > Theory 2: postgres is installed into some directory not in the OP's > > PATH, such as /usr/sbin. Since it's primarily used as a daemon, > > this'd be a reasonable thing for a packager to do. > > Yes in: > > ls -al /usr/lib/postgresql/16/bin/ > > > > > I'd bet a nickel on #1, though, because I've not seen too many > > packagers put postgres somewhere other than where they put psql. > > "Separate server package" is extremely common though. > > Correct. The missing part is that in Debian/Ubuntu packaging when you > use psql you are actually doing: > > ls -al /usr/bin/psql > lrwxrwxrwx 1 root root 37 Aug 8 07:37 /usr/bin/psql -> > ../share/postgresql-common/pg_wrapper > > The Debian packaging routes most things through > pg_wrapper/postgresql-common a Perl script that does the magic of > finding the correct binaries for each Postgres version. > > > > > regards, tom lane > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > > > -- Muhammad Ikram
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 add in PATH. Hope this helps. No what you want to do is read: man pg_wrapper and the other Debian specific commands man pg_lscluster, pg_ctlcluster, pg_dropcluster, etc. Learn what the packaging provides instead of fighting it. Regards, Ikram Muhammad Ikram -- Adrian Klaver adrian.kla...@aklaver.com
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 16.3-1.pgdg120+1)* > > Obviously postgres is not in the path, but I don't know where the > 'apt-get' installed it or why it did not add it to the path. > > Is there a way I can locate the installation directory? > pg_config | grep BINDIR -- Death to America, and butter sauce. Iraq lobster!
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 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: > >> 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: 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 I don't know where the >>> 'apt-get' installed it or why it did not add it to the path. >>> >>> Is there a way I can locate the installation directory? >>> >>> Thank in advance. >>> -- >>> *ArbolOne.ca* Using Fire Fox and Thunderbird. ArbolOne is composed of >>> students and volunteers dedicated to providing free services to charitable >>> organizations. ArbolOne on Java Development is in progress [ í ] >>> >> > > -- > Muhammad Ikram > >
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 > > /var/lib/ > pgsql/db2.txt > diff db1.txt db2.txt > > By executing above queries, if diff is null then it means there is no > difference between source and destination databases. But on the other hand, if the diff is null, it doesn't mean there is a (meaningful) difference between the databases. For example, pg_dump records version information at the start: -- Dumped from database version 14.13 (Ubuntu 14.13-0ubuntu0.22.04.1) -- Dumped by pg_dump version 14.13 (Ubuntu 14.13-0ubuntu0.22.04.1) If your target machine uses a slightly different version of postgres or a different OS, these will be different. And an md5sum only tells you that something is different, not what is different. There might also be small, inconsequential differences in the data. While it is very likely that a dump just after a restore returns rows in a table in the same order, it is not guaranteed. If either the source or the destination database was in use after the restore, some data may have changed. And so on. > On Thu, 22 Aug 2024 at 16:06, 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? And of course your method doesn't check at all whether "user ID and access works liked how it was in the old server". hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
