How to synchronize the read/write DB on my laptop with the read-only DB on cloud (primary on premises, replica on cloud)?
Summary --- I need to set up a read-only DB on the cloud which will synchronize with the read/write DB on my laptop whenever possible (that is, whenever I connect my laptop to the Internet). Need some pointers on what sort of replication that I'm supposed to use and how to set it up. Details --- Hello, I'm developing a database that will be used for business intelligence. That is, there are some CSV reports. Using them, I've created a data model and I'm parsing and loading the reports to the local DB that I've created. The business team needs to be able to read this data in order to use them in their analyses (such as using tools like PowerBI), so I need to put this data somewhere that's always reachable from the Internet. Thing is, the local DB on my laptop is always in development. That is, the tables keep changing, views keep changing, data keeps being changed, etc. Developing directly on the cloud DB is slow. Furthermore, I would need constant Internet access to develop on the cloud DB. Hence, I'm thinking that if there was a solution that will allow me to develop on my laptop and eventually (doesn't need to be instant) synchronizes my local DB with the cloud DB (both the data model and the data itself) automatically, whenever possible (whenever I connect my laptop to the Internet), I believe this would be the solution that I need. I've read the documentation but it's pretty complex. I got the impression that streaming replication would be what I need but it mentions something like the standby server connecting to the primary server. This would not be possible since the primary server is in a laptop, which is not reachable from the public Internet. I believe this could be worked around by using a VPN and connecting both my laptop and the cloud DB to the same VPN. For reference, I'm using AWS RDS if it makes any difference. Regards
trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
Hello, I am having trouble restoring a database backed up with pg_dump/pg_dump_all. The error messages I get are below and appear to suggest that certain sql-language or pl/pgsql-language functions which include an sql statement referencing a table are trying to be executed before the table which they reference has been created. I am surprised that pg_dump could get the order wrong, but that is what is happening. Looking at the first of the error messages, the pl/pgsql function is created as create function public.check_account_from_bill_items(character...) on line 95, but the instruction to create the accounts table '... create table public.accounts ... ' appears on line 510. Consequently, the restore does not work because the relations are created in the wrong order. Any ideas how I can solve it? Please see the error message below: psql:20230913_1300.sql: ERROR: relation "accounts" does not existLINE 1: select name from accounts where (type='Expense' or ... ^QUERY: select name from accounts where (type='Expense' or type='Asset' or type='Stock') and name=accountCONTEXT: PL/pgSQL function public.check_account_from_bill_items(character varying) line 6 at SQL statementCOPY bill_items, line 1: "4096 Website 0 11.6094 \N \N 0 11.6094 5852 1 \N" psql:20230913_1300.sql:201718: ERROR: relation "ebooks" does not existLINE 1: select count(*) from ebooks where isbn13=$1 ^QUERY: select count(*) from ebooks where isbn13=$1 CONTEXT: SQL function "ebook_records_with_isbn13" during inliningCOPY book_information, line 1: "Title abcd \N \N 1---1 111-1---1 11.11 111.11 1 \N ..." psql:20230913_1300.sql:1181710: ERROR: relation "book_information" does not existLINE 1: select e_isbn from book_information where title=$1 limit 1 ^QUERY: select e_isbn from book_information where title=$1 limit 1CONTEXT: SQL function "e_isbn_of" during inliningCOPY ebooks, line 1: "XYZ \N ..." psql:20230913_1300.sql:2670734: ERROR: relation "book_information" does not existLINE 1: select id from book_information where (title,bindin... ^QUERY: select id from book_information where (title,binding,LOWER(isbn))=(title1,binding1,LOWER(isbn1))CONTEXT: PL/pgSQL function public.in_book_information(character varying,character varying,character varying) line 6 at SQL statementCOPY invoice_details, line 1: "vv Cr 200800432 29243 ..." psql:20230913_1300.sql:20459005: ERROR: insert or update on table "bi_authors" violates foreign key constraint "bi_authors_book_information_id_fkey"DETAIL: Key (book_information_id)=(10442) is not present in table "book_information". psql:20230913_1300.sql:20459125: ERROR: insert or update on table "book_praise" violates foreign key constraint "book_praise_title_fkey"DETAIL: Key (title, binding, isbn)=(XXCCC, back, -55) is not present in table "book_information". psql:20230913_1300.sql:20459381: ERROR: insert or update on table "posters" violates foreign key constraint "posters_isbn_fkey"DETAIL: Key (isbn)=(1---3) is not present in table "book_information". psql:20230913_1300.sql:20459413: ERROR: insert or update on table "preorder_items" violates foreign key constraint "preorder_items_title_fkey"DETAIL: Key (title, binding, isbn)=(, , x) is not present in table "book_information". psql:20230913_1300.sql:20459461: ERROR: insert or update on table "purchase_orders" violates foreign key constraint "purchase_orders_isbn_fkey"DETAIL: Key (isbn)=(2-2-12-1) is not present in table "book_information". psql:20230913_1300.sql:20459469: ERROR: insert or update on table "purchase_orders" violates foreign key constraint "purchase_orders_item_fkey"DETAIL: Key (item, type_or_binding, isbn)=(title, back, ) is not present in table "book_information". psql:20230913_1300.sql:20459653: ERROR: insert or update on table "recurring_orders_detail" violates foreign key constraint "recurring_orders_detail_isbn_fkey"DETAIL: Key (isbn)=(0--1) is not present in table "book_information". psql:20230913_1300.sql:20459701: ERROR: insert or update on table "royalties_paid" violates foreign key constraint "royalties_paid_bill_item_id_fkey"DETAIL: Key (bill_item_id)=(3151) is not present in table "bill_items". psql:20230913_1300.sql:20459765: ERROR: insert or update on table "spine_locations" violates foreign key constraint "spine_locations_isbn_fkey"DETAIL: Key (isbn)=(0-0) is not present in table "book_information". psql:20230913_1300.sql:20459773: ERROR: insert or update on table "stock_boxed" violates foreign key constraint "stock_boxed_isbn_fkey"DETAIL: Key (isbn)=(1-2121) is not present in table "book_information". psql:20230913_1300.sql:20459781: ERROR: insert or update on table "stock_boxed" violates foreign key constraint "stock_
Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
On 9/18/23 08:08, Harry Green wrote: Hello, I am having trouble restoring a database backed up with pg_dump/pg_dump_all. The error messages I get are below and appear to suggest that certain sql-language or pl/pgsql-language functions which include an sql statement referencing a table are trying to be executed before the table which they reference has been created. I am surprised that pg_dump could get the order wrong, but that is what is happening. Looking at the first of the error messages, the pl/pgsql function is created as /*create function public.check_account_from_bill_items(character...) */on line 95, but the instruction to create the accounts table /*'... create table public.accounts ... '*/ appears on line 510. I'm betting this a function being used in a table CHECK and per: https://www.postgresql.org/docs/current/sql-createtable.html "Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row (see Section 5.4.1). The system column tableoid may be referenced, but not any other system column." There is no dependency checking for CHECK functions. Consequently, the restore does not work because the relations are created in the wrong order. Any ideas how I can solve it? Please see the error message below: Thanks a lot! -- Adrian Klaver adrian.kla...@aklaver.com
Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
On Mon, Sep 18, 2023 at 8:32 AM Harry Green wrote: > Any ideas how I can solve it? > It is most likely you have broken some kind of rule in your database schema, but without seeing the dump file it is impossible to point out what you have done wrong. Relatedly, the bodies of functions are black-boxes, it is not possible to establish dependencies between them. This limitation is why many of the rules I allude to above exist. You have also not mentioned what version you are working with. David J.
Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
On 9/18/23 08:16, Adrian Klaver wrote: On 9/18/23 08:08, Harry Green wrote: Hello, I am having trouble restoring a database backed up with pg_dump/pg_dump_all. The error messages I get are below and appear to suggest that certain sql-language or pl/pgsql-language functions which include an sql statement referencing a table are trying to be executed before the table which they reference has been created. I am surprised that pg_dump could get the order wrong, but that is what is happening. Looking at the first of the error messages, the pl/pgsql function is created as /*create function public.check_account_from_bill_items(character...) */on line 95, but the instruction to create the accounts table /*'... create table public.accounts ... '*/ appears on line 510. I'm betting this a function being used in a table CHECK and per: https://www.postgresql.org/docs/current/sql-createtable.html "Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row (see Section 5.4.1). The system column tableoid may be referenced, but not any other system column." There is no dependency checking for CHECK functions. Forgot to add to above, that if you want to do this sort of thing then use a trigger. In a dump/restore they are added back to the tables after the tables and table data have been restored. Consequently, the restore does not work because the relations are created in the wrong order. Any ideas how I can solve it? Please see the error message below: Thanks a lot! -- Adrian Klaver adrian.kla...@aklaver.com
Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
On Mon, 2023-09-18 at 15:08 +, Harry Green wrote: > I am having trouble restoring a database backed up with pg_dump/pg_dump_all. > The error messages I get are below and appear to suggest that certain > sql-language or > pl/pgsql-language functions which include an sql statement referencing a > table are > trying to be executed before the table which they reference has been created. > I am surprised that pg_dump could get the order wrong, but that is what is > happening. > > Looking at the first of the error messages, the pl/pgsql function is created > as > create function public.check_account_from_bill_items(character...) on line > 95, > but the instruction to create the accounts table '... create table > public.accounts ... ' > appears on line 510. > > Consequently, the restore does not work because the relations are created in > the > wrong order. Any ideas how I can solve it? > > Please see the error message below: > > psql:20230913_1300.sql: ERROR: relation "accounts" does not exist > LINE 1: select name from accounts where (type='Expense' or ... > ^ > QUERY: select name from accounts where (type='Expense' or > type='Asset' or type='Stock') and name=account > CONTEXT: PL/pgSQL function public.check_account_from_bill_items(character > varying) line 6 at SQL statement > COPY bill_items, line 1: "4096 Website 0 11.6094 \N \N 0 > 11.6094 5852 1 \N" > > psql:20230913_1300.sql:201718: ERROR: relation "ebooks" does not exist > LINE 1: select count(*) from ebooks where isbn13=$1 > ^ > QUERY: select count(*) from ebooks where isbn13=$1 > CONTEXT: SQL function "ebook_records_with_isbn13" during inlining > COPY book_information, line 1: "Title abcd \N \N 1---1 > 111-1---1 11.11 111.11 1 \N ..." > > [...] > > psql:20230913_1300.sql:20459125: ERROR: insert or update on table > "book_praise" violates foreign key constraint "book_praise_title_fkey" > DETAIL: Key (title, binding, isbn)=(XXCCC, back, -55) is not present in > table "book_information". > > psql:20230913_1300.sql:20459381: ERROR: insert or update on table "posters" > violates foreign key constraint "posters_isbn_fkey" > DETAIL: Key (isbn)=(1---3) is not present in table > "book_information". > > [...] It seems like you are restoring the dump in the wrong way. pg_dump dumps foreign key constraints last of all, so during a restore they are created after all the data have been inserted into the tables, and the error you see can never happen. You must be restoring a data-only dump into an already existing schema with foreign keys in place. That won't work and isn't supported. Don't forget that foreign keys can be circular, and there might be no "correct order" to dump the tables. The first errors with the function calls are less obvious, but existing triggers on the tables micht be an explanation. Since one of the functions is called "check_...", another explanation could be that you have check constraints that use functions that access other tables. That won't work and is not allowed. Without knowing more, I cannot be certain what exactly is wrong, but it doesn't look like a PostgreSQL bug to me. Perhaps you can provide more details. Yours, Laurenz Albe
Connection not allowed because of an error 'Not in pg_hba.conf'
I am doing some testing trying to migrate some websites from using Oracle to Postgres. (Using Perl DBI and DBD::Pg as the connecting mechanism) (Server Environment Postgres 15 running on Ubuntu 22.04, client Rocky Linux using the supplied PostgreSQL DBI and DBD::Pg packages) The error I am getting on the client is: password authentication failed for user "trav" connection to server at "dhbpostgres.pharmacy.arizona.edu" (10.128.206.109), port 5432 failed: FATAL: no pg_hba.conf entry for host "10.128.206.109", user "trav", database "webdata", no encryption But I do have an entry that should allow it: #Internal server mgmt range hostssl all all 10.128.206.0/23 password >From the manual (pg 704, 21.1. The pg_hba.conf File): "An IP address range is specified using standard numeric notation for the range's starting address, then a slash (/) and a CIDR mask length. The mask length indicates the number of high-order bits of the client IP address that must match. Bits to the right of this should be zero in the given IP address. There must not be any white space between the IP address, the /, and the CIDR mask length. Typical examples of an IPv4 address range specified this way are 172.20.143.89/32 for a single host, or 172.20.143.0/24 for a small network, or 10.6.0.0/16 for a larger one. " 10.128.206.109 is definitely in that range. The test script DOES work with my desktop running the same software, but I have it set in pg_hba.conf as just my systems ip: hostssl webdata trav nnn.nnn.nnn.nnn/32 password (Ip address redacted because it is externally accessible) -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Connection not allowed because of an error 'Not in pg_hba.conf'
On 9/18/23 12:45, Johnson, Bruce E - (bjohnson) wrote: I am doing some testing trying to migrate some websites from using Oracle to Postgres. (Using Perl DBI and DBD::Pg as the connecting mechanism) (Server Environment Postgres 15 running on Ubuntu 22.04, client Rocky Linux using the supplied PostgreSQL DBI and DBD::Pg packages) The error I am getting on the client is: password authentication failed for user "trav" connection to server at "dhbpostgres.pharmacy.arizona.edu" (10.128.206.109), port 5432 failed: FATAL: no pg_hba.conf entry for host "10.128.206.109", user "trav", database "webdata", no encryption But I do have an entry that should allow it: #Internal server mgmt range hostsslallall10.128.206.0/23 password I guessing you want that to be: 10.128.206.109/32 or 10.128.206.0/24 -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs -- Adrian Klaver adrian.kla...@aklaver.com
Re: Connection not allowed because of an error 'Not in pg_hba.conf'
> On Sep 18, 2023, at 11:45 AM, Johnson, Bruce E - (bjohnson) > wrote: > > I am doing some testing trying to migrate some websites from using Oracle to > Postgres. (Using Perl DBI and DBD::Pg as the connecting mechanism) > > (Server Environment Postgres 15 running on Ubuntu 22.04, client Rocky Linux > using the supplied PostgreSQL DBI and DBD::Pg packages) > > The error I am getting on the client is: > > password authentication failed for user "trav" > connection to server at "dhbpostgres.pharmacy.arizona.edu" (10.128.206.109), > port 5432 failed: FATAL: no pg_hba.conf entry for host "10.128.206.109", > user "trav", database "webdata", no encryption > > But I do have an entry that should allow it: > > #Internal server mgmt range > hostssl > all all > 10.128.206.0/23 password > I might be missing something obvious, but your error says “no encryption”, while the pg_hba entry is “hostssl” indicating it will match encrypted connections only, so it doesn’t match. --- Israel Brewster Software Engineer Alaska Volcano Observatory Geophysical Institute - UAF 2156 Koyukuk Drive Fairbanks AK 99775-7320 Work: 907-474-5172 cell: 907-328-9145 > From the manual (pg 704, 21.1. The pg_hba.conf File): > > "An IP address range is specified using standard numeric notation for the > range's starting address, then a slash (/) and a CIDR mask length. The mask > length indicates the number of high-order bits of the client IP address that > must match. Bits to the right of this should be zero in the given IP address. > There must not be any white space between the IP address, the /, and the CIDR > mask length. > > Typical examples of an IPv4 address range specified this way are > 172.20.143.89/32 for a single host, or 172.20.143.0/24 for a small network, > or 10.6.0.0/16 for a larger one. " > > 10.128.206.109 is definitely in that range. > > The test script DOES work with my desktop running the same software, but I > have it set in pg_hba.conf as just my systems ip: > > hostssl > webdata > trav nnn.nnn.nnn.nnn/32 > password > > > (Ip address redacted because it is externally accessible) > > -- > Bruce Johnson > University of Arizona > College of Pharmacy > Information Technology Group > > Institutions do not have opinions, merely customs > >
Re: Connection not allowed because of an error 'Not in pg_hba.conf'
"Johnson, Bruce E - (bjohnson)" writes: > The error I am getting on the client is: > password authentication failed for user "trav" > connection to server at "dhbpostgres.pharmacy.arizona.edu" (10.128.206.109), > port 5432 failed: FATAL: no pg_hba.conf entry for host "10.128.206.109", > user "trav", database "webdata", no encryption > But I do have an entry that should allow it: > #Internal server mgmt range > hostssl all all 10.128.206.0/23 password I think what you are seeing here is two separate connection attempts. libpq will try an SSL connection, and that one is seemingly failing with a bad password. Then it'll try a non-SSL connection, and that one is getting rejected by the server because "hostssl" doesn't permit it, leading to your second message (which clearly shows that that connection wasn't ssl-encrypted). You could adjust your connection parameters on the client side to prevent the useless non-SSL connection attempt. But of course the real question is how come the password authentication failed in the first attempt. Looking into the server's log might yield a clue. regards, tom lane
Re: [EXT]Re: Connection not allowed because of an error 'Not in pg_hba.conf'
On 9/18/23 14:46, Johnson, Bruce E - (bjohnson) wrote: Reply to list also. Ccing list to return your answer there. It turned out that for some reason the perl DBD::Pg module was trying to connect twice, the first failed because of a typo in the password, the second because it tried to connect without ssl. I am not sure why it tried twice. -- Adrian Klaver adrian.kla...@aklaver.com -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs -- Adrian Klaver adrian.kla...@aklaver.com
[Beginner Question]What should I do if I want to achieve lsm-tree index in postgresql?
Hi community, I am a student and I want to achieve lsm-tree index in postgresql, for my database experiment, I have already read the document of postgresql, and some postgresql's moudle source, but I really don't know where to start this work. Make a new extension like 'https://github.com/HypoPG/hypopg'? Or directly change the kernel? Can someone give me some advice? Thanks in advance! Yours, Wen Yi
回复: ident auth does not works as usual
HI I've got the right meanng of pg_hba.conf and pg_ident.conf. My previous understanding was incorrect. I thought pgserver would automatically handle the conversion between OS users and PostgreSQL roles. Thanks all. 发件人: David G. Johnston 发送时间: 2023年8月28日 14:44 收件人: pan snowave 抄送: Alban Hertroys ; pgsql-general@lists.postgresql.org 主题: Re: ident auth does not works as usual On Mon, Aug 28, 2023 at 7:34 AM pan snowave mailto:win...@live.cn>> wrote: But when i connect to psserver by using os user root /usr/local/pgsql/bin/psql -p5432 -d db1 -h127.0.0.1 If you want to login as the cce role add " -U cce " to your command line (or one of the various other ways you can specify a db role name via the cli or libpq) psql -U cce -h 127.0.0.1 -d db1 Keep in mind you could very well have two lines in your pg_ident.conf file: test root cce test root ddf David J.
