Re: Connecting to Postgresql without IP address
Hello, Using PostgreSQL 11 Currently connecting PostgreSQL database from our Powerbuilder application using the IPAddress as Server Name through ODBC driver. Would like to know whether we can connect naming the computername instead of IP Address ? If yes, what info to be entered in pg_hba.config file ? My connection string is :"ConnectString='Driver=PostgreSQL Unicode;Database=dbname;Server=192.168.1.21;Port=5432;UID=user;PWD=pass;',DisableBind=1" Here Server is assigned with IP Address of the computer where PostgreSQL is installed. I think of doing it with "ConnectString='Driver=PostgreSQL Unicode;Database=dbname;Server=PGServer;Port=5432;UID=user;PWD=pass;',DisableBind=1" Is it possible ? Happiness AlwaysBKR Sivaprakash
Re: Connecting to Postgresql without IP address
Thanks Ben, It's NOT working. Already an entry similar to this is there host all all 192.168.1.0 255.255.255.0 md5 And I added the following entry also in the hba file and restarted PG Serverhostssl all all 192.168.1.0/16 md5 And tried to connect using server computer name instead of IP address. I get an error, when I try to add and test and ODBC entry Any thing else I need to do ? Happiness Always BKR Sivaprakash On Thursday, 1 July, 2021, 06:15:45 am IST, Ben Madin wrote: Your connection string will work as long as there is a DNS entry for PGServer. Your pg_hba.conf will however need to have an IP address/netmask. So if you are connecting from within your local network, you can put something like: hostssl all all 192.168.0.0/16 md5 Hth? On Wed, 30 Jun 2021 at 15:54, sivapostg...@yahoo.com wrote: Hello, Using PostgreSQL 11 Currently connecting PostgreSQL database from our Powerbuilder application using the IPAddress as Server Name through ODBC driver. Would like to know whether we can connect naming the computername instead of IP Address ? If yes, what info to be entered in pg_hba.config file ? My connection string is :"ConnectString='Driver=PostgreSQL Unicode;Database=dbname;Server=192.168.1.21;Port=5432;UID=user;PWD=pass;',DisableBind=1" Here Server is assigned with IP Address of the computer where PostgreSQL is installed. I think of doing it with "ConnectString='Driver=PostgreSQL Unicode;Database=dbname;Server=PGServer;Port=5432;UID=user;PWD=pass;',DisableBind=1" Is it possible ? Happiness AlwaysBKR Sivaprakash -- -- Sent from my iPhone.
Re: How to detach a database
Hello, We want to carry out some data manipulation work in a database exclusively. We don't want any one to connect this database during that time. How to do it ? In SQL Server there are two ways [ earlier we used SQL Server and now switched to PG ]1. Change the database to work in single-user mode.2. Detach the database from that server, copy to another server, do the required changes, copy back to original server and attach it. Yes, we do carry out this in the production database also, where we used to ask the users not to use the application. Is there any way to make a PG database either in-active or switch to single-user mode ? We want to avoid DELETE database process. Happiness AlwaysBKR Sivaprakash
Re: New Server for PostgreSQL
Hello, Planning to go for a new database server. Should opt for more cores or higher GHz ? Using one base server (with SQL Server) which is ageing. Now switched to PostgreSQL. More load will be there during festival seasons (Deepavali) only and we need to plan for that load only. There will be around 25 clients working during festival seasons and 10 tablets for billings, where slow down (write speed is required) should not occur while writing to db. Any suggested server for this load. Happiness Always BKR Sivaprakash
Re: Error message while trying to connect from PGAdmin 4
Hello PostgreSQL 11.11, PGAdmin 4.27, Windows 10 Pro 20H2 Working fine till yesterday (24.11.2021). When we tried to connect from PGAdmin, it refused to list server list. Thought of some corruption happened, we tried to add a server we got the following error message What could be the reason for this error? Any steps to diagnose this issue? When we try to connect the same PG Server from other machines, it's working fine. This issue happens when we try to connect from the same machine where PG is installed. Happiness Always BKR Sivaprakash
Re: Error message while trying to connect from PGAdmin 4
Corrupt windows user was the reason that we found. When tried from other windows users, it's working. So switched to a new windows user and everything seems perfect.Thanks. On Thursday, 25 November, 2021, 11:11:56 am IST, sivapostg...@yahoo.com wrote: Hello PostgreSQL 11.11, PGAdmin 4.27, Windows 10 Pro 20H2 Working fine till yesterday (24.11.2021). When we tried to connect from PGAdmin, it refused to list server list. Thought of some corruption happened, we tried to add a server we got the following error message What could be the reason for this error? Any steps to diagnose this issue? When we try to connect the same PG Server from other machines, it's working fine. This issue happens when we try to connect from the same machine where PG is installed. Happiness Always BKR Sivaprakash
Re. Backup of postgresql database
Hello,Got few (currently 3 will increase as days go) database in A2 hosting unmanaged vps server, running Ubuntu server 18 lts Planning to have an additional backup of all files, including pg database, to another cloud provider like Amazon S3 (any other suggestions?) Size won't be crossing 5GB per database for the next few years. How this could be done? Happiness AlwaysBKR Sivaprakash Sent from Yahoo Mail on Android
Re: Re. Backup of postgresql database
Not done so far. Started using A2 for production just a couple of months back. Sent from Yahoo Mail on Android On Sat, Feb 5, 2022 at 9:19 AM, Ron wrote:On 2/4/22 9:14 PM, sivapostg...@yahoo.com wrote: Hello, Got few (currently 3 will increase as days go) database in A2 hosting unmanaged vps server, running Ubuntu server 18 lts Planning to have an additional backup of all files, including pg database, to another cloud provider like Amazon S3 (any other suggestions?) Size won't be crossing 5GB per database for the next few years. How are you currently backing up the databases? -- Angular momentum makes the world go 'round.
Re: Re. Backup of postgresql database
Should manually do pg_dump and then copy the resultant one to the destination? Sent from Yahoo Mail on Android On Sat, Feb 5, 2022 at 10:32 AM, Benedict Holland wrote: Pg_dump or do a wal archive. Thanks,Ben On Fri, Feb 4, 2022, 10:49 PM Ron wrote: On 2/4/22 9:14 PM, sivapostg...@yahoo.com wrote: Hello, Got few (currently 3 will increase as days go) database in A2 hosting unmanaged vps server, running Ubuntu server 18 lts Planning to have an additional backup of all files, including pg database, to another cloud provider like Amazon S3 (any other suggestions?) Size won't be crossing 5GB per database for the next few years. How are you currently backing up the databases? -- Angular momentum makes the world go 'round.
Re: Creation of FK without enforcing constraint for existing data
Hello, I need to create a Foreign Key for a table without enforcing the constraint for existing data. Few orphan exists in existing data, which we plan to resolve it later. We use the following query to create the FK [ Which of course checks for the presence of record in referencing table] ALTER TABLE public.table1 ADD CONSTRAINT "constraint1_FK" FOREIGN KEY (field1) REFERENCES public.tabnle2(field2) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT; The facility NOT to check for the presence of the parent record is available in SQL Server. Searching an equivalent option in PostGreSQL. Is it available? Else any other way to achieve this. Happiness Always BKR Sivaprakash
Re: Behavior of identity columns
Hello,
I see 'identity' column values increment even when some error(s) occurs while
inserting data. Is that a known bug or known behavior?
Create script of table: CREATE TABLE public.users(
id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1
MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ), username character varying(255)
COLLATE pg_catalog."default", email character varying(255) COLLATE
pg_catalog."default", first_name character varying(255) COLLATE
pg_catalog."default", last_name character varying(255) COLLATE
pg_catalog."default", CONSTRAINT users_pkey PRIMARY KEY (id), CONSTRAINT
"test_UX" UNIQUE (username))WITH ( OIDS = FALSE)TABLESPACE pg_default;
ALTER TABLE public.users OWNER to
postgres;***column id is int and identity
columncolumn username is unique
When I insert data that violates the unique constraint, I see that the id value
gets incremented. And I see missing values in between when I add the correct
data next time.
my Insert SQLinsert into users (username, email, first_name, last_name) values
('ONE', 'o...@gmail.com', 'one', '1'); // id = 1insert into users (username,
email, first_name, last_name) values ('ONE', 'o...@gmail.com', 'one', '1'); //
Insert failsinsert into users (username, email, first_name, last_name) values
('TWO', 'o...@gmail.com', 'one', '1'); // id = 3insert into users0(username,
email, first_name, last_name) values ('TWO', 'o...@gmail.com', 'one', '1'); //
insert fails as there is no users0 tableinsert into users (username, email,
first_name, last_name) values ('THREE', 'o...@gmail.com', 'one', '1'); // id = 5
ID gets incremented even when some wrong table name is mentioned in the query.
Check 4th query.
Is it a known behavior or a known bug?
Happiness AlwaysBKR Sivaprakash
Re: Creating constraint dynamically
Hello,Using PG 11.4 We populate constraint string dynamically and add it to the table with alter table command. It gets added, but without the required brackets. What we build isALTER TABLE public.tx_barcode_stock ADD CONSTRAINT "tx_barcode_stock_CK1" CHECK ( (branchcode = '1'::bpchar and barcodeitem = 'Y'::bpchar and closingstock >= 0::numeric) Or (branchcode = '1' and barcodeitem = 'N'::bpchar and closingstock >= 0::numeric ) Or (branchcode = '2'::bpchar and barcodeitem = 'Y'::bpchar and closingstock >= 0::numeric) Or (branchcode = '2' and barcodeitem = 'N'::bpchar and closingstock >= 0::numeric ) ) NOT VALID; After creation, when we check what we find is [ in PgAdmin ]ALTER TABLE public.tx_barcode_stock ADD CONSTRAINT "tx_barcode_stock_CK1" CHECK (branchcode::bpchar = '1'::bpchar AND barcodeitem = 'Y'::bpchar AND closingstock >= 0::numeric OR branchcode::text = '1'::text AND barcodeitem = 'N'::bpchar AND closingstock >= 0::numeric OR branchcode::bpchar = '2'::bpchar AND barcodeitem = 'Y'::bpchar AND closingstock >= 0::numeric OR branchcode::text = '2'::text AND barcodeitem = 'N'::bpchar AND closingstock >= 0::numeric) NOT VALID; We have only one bracket, in the final updated one. Since there are AND and OR conditions, without brackets the whole conditions becomes useless. How to create a constraint like the above one, with braces in tact ? Or any other way that we can implement a check constraint as above? Happiness Always BKR Sivaprakash
Re: Creating constraint dynamically
Hello, Sorry for delay... I had to go on leave.. Checked, it's our mistake here. The issue is not what I described, but in the replicated database where the constraint was not updated. Correcting the constraint there solved this issue. Sorry for not checking properly before coming here. Happiness Always BKR Sivaprakash On Monday, 22 August, 2022 at 03:54:30 pm IST, jian he wrote: On Mon, Aug 22, 2022 at 12:59 PM sivapostg...@yahoo.com wrote: Hello,Using PG 11.4 We populate constraint string dynamically and add it to the table with alter table command. It gets added, but without the required brackets. What we build isALTER TABLE public.tx_barcode_stock ADD CONSTRAINT "tx_barcode_stock_CK1" CHECK ( (branchcode = '1'::bpchar and barcodeitem = 'Y'::bpchar and closingstock >= 0::numeric) Or (branchcode = '1' and barcodeitem = 'N'::bpchar and closingstock >= 0::numeric ) Or (branchcode = '2'::bpchar and barcodeitem = 'Y'::bpchar and closingstock >= 0::numeric) Or (branchcode = '2' and barcodeitem = 'N'::bpchar and closingstock >= 0::numeric ) ) NOT VALID; After creation, when we check what we find is [ in PgAdmin ]ALTER TABLE public.tx_barcode_stock ADD CONSTRAINT "tx_barcode_stock_CK1" CHECK (branchcode::bpchar = '1'::bpchar AND barcodeitem = 'Y'::bpchar AND closingstock >= 0::numeric OR branchcode::text = '1'::text AND barcodeitem = 'N'::bpchar AND closingstock >= 0::numeric OR branchcode::bpchar = '2'::bpchar AND barcodeitem = 'Y'::bpchar AND closingstock >= 0::numeric OR branchcode::text = '2'::text AND barcodeitem = 'N'::bpchar AND closingstock >= 0::numeric) NOT VALID; We have only one bracket, in the final updated one. Since there are AND and OR conditions, without brackets the whole conditions becomes useless. How to create a constraint like the above one, with braces in tact ? Or any other way that we can implement a check constraint as above? Happiness Always BKR Sivaprakash I guess it's because pgadmin uses pg_get_constraintdef ( constraint oid [, pretty boolean ] ) pretty is true.if you use pg_get_constraintdef ( constraint oid ,false ) you will see more braces. -- I recommend David Deutsch's <> Jian
Re: Query Performance
Hello, My query is like this Select a.field1, a.field2, a.field3From (Select a.field1, b.field2, c.field3 From table1 a Join table2 b on b.something = a.something Join table3 c On c.something = a.something Where a.field7 = 'value' UNION ALL Select a.field4, a.field5, a.field6 From table11 a Join table21 b On b.something = a.something Where a.field8 = 'something' ) aJoin table10 bOn b.field11 = (Select c.field11 From table10 c Where c.field10 = a.field1 ) <- instead of a.field1, if I hardcode value (eg. '100') query runs fasterJoin table21 cOn c.something = a.something... In the above query, If I substitute a value for a.field1, query runs faster. Any suggestion/guidance/links to improve the query performance without substituting the value ? Happiness Always BKR Sivaprakash
Re: Query Performance
I should have given you the full query. Here it is Select a.locationname, a.usercode, a.itemname, a.uomname, a.batchnumber, a.expirydate, a.itemnamefk, a.itemuomfk, f.itemgroupname, a.locationpk, Sum(a.quantity) as quantity, Sum(a.freequantity) as freequantity, b.beforetaxrate as beforetaxrate, (b.rate / b.rateper) as purchaserate, b.netrate as netrate, b.netsalesrate as netsalesrate, b.effectiverate as effectiverate, b.rateper, a.reportformat, g.standardcost, g.defaultpurchaserate, g.salesrateone, g.salesratetwo, g.salesratethree, g.salesratefour, g.salesratefive, g.salesratesix, g.salesrateseven, g.salesrateeight, g.salesratenine, 'N' as negativerate From (Select k.locationname, a.usercode, a.itemname, c.uomname, j.batchnumber, j.expirydate, j.itemnamefk, j.itemuomfk, k.locationpk, j.receivedquantity as quantity, j.receivedfreequantity as freequantity, c.reportformat From in_item_name a Join in_item_uom b On b.itemnamefk = a.itemnamepk Join gl_uom c On c.uompk = b.uomfk Join view_item_receipts j On j.itemnamefk = a.itemnamepk And j.itemuomfk = b.itemuompk Join in_location k On k.locationpk = j.locationfk Where j.companycode = 'SDM' And j.branchcode = '001' And j.accountperiodid = 1 And j.voucherdate <= '2022/09/17' And j.billstatus <> 'C' And j.topparentcode <> 4 And (j.receivedquantity <> 0 Or j.receivedfreequantity <> 0) UNION ALL Select k.locationname, a.usercode, a.itemname, c.uomname, j.batchnumber, j.expirydate, j.itemnamefk, j.itemuomfk, k.locationpk, (j.issuedquantity * -1) as quantity, (j.issuedfreequantity * -1) as freequantity, c.reportformat From in_item_name a Join in_item_uom b On b.itemnamefk = a.itemnamepk Join gl_uom c On c.uompk = b.uomfk Join view_item_issues j On j.itemnamefk = a.itemnamepk And j.itemuomfk = b.itemuompk Join in_location k On k.locationpk = j.locationfk Where j.companycode = 'SDM' And j.branchcode = '001' And j.accountperiodid = 1 And j.voucherdate <= '2022/09/17' And j.billstatus <> 'C' And j.topparentcode <> 4 And (j.issuedquantity <> 0 Or j.issuedfreequantity <> 0)) a Left Outer Join view_item_receipts b <- It's actually a view of 4 tables which tries to arrive the last purchase rate On b.itemreceiptspk = (Select c.itemreceiptspk From view_item_receipts c Where c.companycode = 'SDM' And c.branchcode = '001' And c.accountperiodid = 1 And c.voucherdate <= '2022/09/17' And c.billstatus <> 'C' And c.itemnamefk = a.itemnamefk And c.itemuomfk = a.itemuomfk And c.batchnumber = a.batchnumber And c.expirydate = a.expirydate And (c.receivedquantity <> 0 Or c.receivedfreequantity <> 0) Order by c.voucherdate desc, c.vouchernumber desc, c.sequencenumber desc Limit 1 ) Join in_item_name c On c.itemnamepk = a.itemnamefk Join in_item_group f On f.itemgrouppk = c.itemgroupfk Left Outer Join in_item_rate g On g.itemuomfk = b.itemuomfk And g.itemnamefk = b.itemnamefk And '2022/09/17' between g.fromdate and g.todate Group By a.locationname, a.usercode, a.itemname, a.uomname, a.batchnumber, a.expirydate, a.itemnamefk, a.itemuomfk, f.itemgroupname, a.locationpk, b.beforetaxrate, b.rate, b.netrate, b.netsalesrate, b.effectiverate, b.rateper, a.reportformat, g.standardcost, g.defaultpurchaserate, g.salesrateone, g.salesratetwo, g.salesratethree, g.salesratefour, g.salesratefive, g.salesratesix, g.salesrateseven, g.salesrateeight, g.salesratenine Having (sum(a.quantity) + sum(a.freequantity)) <> 0 Order by 1, 3, 2, 5 Create a index for companycode, branchcode, c.accountperiodid, voucherdate, billstatus, itemnamefk, itemuomfk, batchnumber, expirydate, receivedquantity, receivedfreequantity in all the 4 tables that this view got. Happiness Always BKR Sivaprakash On Saturday, 17 September, 2022 at 03:33:48 pm I
Re: Can I trigger an action from a coalesce ?
It could also be done by putting those values in square bracket, if substituted with default values.eg. [0] 4 45 [100]Values within square brackets are default values. On Sunday, 23 February, 2020, 04:52:11 pm IST, Peter J. Holzer wrote: On 2020-02-22 16:02:06 -0500, stan wrote: > I have a case where if a value does not exist, I am going to use a default, > which is easy with coalesce. But I would like to warn the user that a > default has been supplied. The default value is reasonable, and could > actually come from the source table, so I can't just check the value. > I'd like to do a raise NOTICE, if the default portion of the coalesce fires. Might I suggest a different approach? Instead of raising a notice, add an additional column. Something like this: wds=> create table mytable (id serial, value int); CREATE TABLE Time: 127.124 ms wds=> insert into mytable (value) values (2), (23), (null), (42), (78); INSERT 0 5 Time: 48.223 ms wds=> select * from mytable; ╔╤═══╗ ║ id │ value ║ ╟┼───╢ ║ 1 │ 2 ║ ║ 2 │ 23 ║ ║ 3 │ (∅) ║ ║ 4 │ 42 ║ ║ 5 │ 78 ║ ╚╧═══╝ (5 rows) Time: 0.657 ms wds=> select id, coalesce(value, 42) as value, value is null as value_was_null wds-> from mytable; ╔╤═══╤╗ ║ id │ value │ value_was_null ║ ╟┼───┼╢ ║ 1 │ 2 │ f ║ ║ 2 │ 23 │ f ║ ║ 3 │ 42 │ t ║ ║ 4 │ 42 │ f ║ ║ 5 │ 78 │ f ║ ╚╧═══╧╝ (5 rows) Time: 0.247 ms This lets the user distinguish the real 42 with id 4 from the substituted 42 with id 3. I think this would be awkward with a notice. At most you could tell the user "some values were substituted", but not which ones (at least not if the query can return a large number of rows). hp -- _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | h...@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"
Re: Backup & Restore
HiCan u suggest a good backup solution for a windows installation ? Looks like the suggested two [ pgbarman, pgbackrest ] works only in Linux. On Tuesday, 25 February, 2020, 01:46:33 am IST, Stephen Frost wrote: Greetings, * Dor Ben Dov (dor.ben-...@amdocs.com) wrote: > What is your backup and restore solution in production when working with > Postgres ? > (+ if you can say few words why you picked this X solution instead of others) I'd recommend pgbackrest- https://www.pgbackrest.org, it's got lots of great features including parallel backup, incremental and differential backups, compression, encryption, and all of those can be used together. pgbackrest also can parallelize WAL shipping if you're writing lots of data. There's other options out there, of course. In any case, I strongly recommend that you use one of the existing solutions and don't try to roll your own. Just to be clear- I'm also involved in the project (though not the primary developer, that's David, who you'll also see on this list and on the -hackers list contributing things to PostgreSQL). Thanks! Stephen
Re: Backup & Restore
We do have plans to move to Linux in the future after the successful implementation of at least 4 or 5 projects. Till then we want to keep windows. We were (are) using SQL Server (also) and this is our first one with Postgres. With our manpower, we feel tough to switch two things (Database & OS) at a time. We'll be using either pg_basebackup or pg_dump, as suitable, till we find a good backup solution. On Tuesday, 25 February, 2020, 07:24:00 pm IST, Stephen Frost wrote: Greetings, * sivapostg...@yahoo.com (sivapostg...@yahoo.com) wrote: > HiCan u suggest a good backup solution for a windows installation ? Looks >like the suggested two [ pgbarman, pgbackrest ] works only in Linux. While it's certainly something we'd like to do, we haven't ported pgbackrest to Windows yet. That said, it's now entirely written in reasonably portable C and so it shouldn't be too much effort to port it. Until that's done though, and I can't say exactly when that port will happen, your best option is probably pg_basebackup. Of course, I'd strongly recommend you consider running PG on Linux instead, particularly for a production environment. Thanks, Stephen
Re: Need to find the no. of connections for a database
Hello, Before taking a few reports, we need to ensure that only one connection is made to the database and all other computers need to close the connection to that database. This is to prevent any additional entry(ies) that could be made during the course of the report taking. This single-user mode is to be there till the report is taken and few entries are passed. How to do it? Is it possible to switch to single-user mode from application and back to multi-user mode once the work is completed? Or any other solution available? Happiness AlwaysBKR Sivaprakash
Re: Need to find the no. of connections for a database
Well, I need to prevent other users from entering any transaction till I finish taking reports from my application. All users will be using the same application, from which this report is supposed to be printed. If they enter any data, those data also need to be taken into account in this report. The process is somehow lengthy that starts from arriving some cumulative value(s) and based on this value, some lengthy process is done. Any data entered in between will affect this report as well the process we do. For this same situation, while using SQL Server, we used to count the number of users of that database and if it's greater than one, we don't start the process. By checking the same no. of users in vantage point, we could achieve the required result. I tried the same way in Postgres, but I could not get the correct connection list, by using this query. SELECT pid, datname, usename, application_name, client_hostname, client_port, backend_start, query_start, query, stateFROM pg_stat_activityWHERE datname = 'databasename'And state = 'active' I expect this query to add the number, when there is another connection from one computer. I couldn't get it when I tried this query from PGAdmin and connected this database from another machine. It's just a connection and no query was executed from that machine. I think setting isolation level will not work out. Switching to single user mode, if available, will be better. Any ideas ? Happiness AlwaysBKR Sivaprakash On Thursday, 27 February, 2020, 04:34:46 pm IST, Ravi Krishna wrote: > > Before taking a few reports, we need to ensure that only one connection is > made to the database and all other computers need to close the connection to > that database. This is to prevent any additional entry(ies) that could be > made during the course of the report taking. This single-user mode is to be > there till the report is taken and few entries are passed. > > How to do it? Is it possible to switch to single-user mode from > application and back to multi-user mode once the work is completed? Or any > other solution available? > > Happiness Always > BKR Sivaprakash > This concept is outdated. You should set your application to correct isolation level to get a consistent state.
Re: Need to find the no. of connections for a database
Hello, I'm saying isolation will not work out to my requirement. The steps. 1. On completion of all entries by all, say for a day.2. Lock, so that no one enters any other data.3. Create a report from the entered data.4. Create / Modify required entries from the values arrived in the report. [ long process ]5. Once completed, commit all data.6. Unlock, so that other users can enter data again. Data entered will be for another date. Data cannot [should] not entered for the processed date. On Thursday, 27 February, 2020, 06:29:00 pm IST, Ireneusz Pluta/wp.pl wrote: W dniu 2020-02-27 o 12:35, sivapostg...@yahoo.com pisze: > I need to prevent other users from entering any transaction till I finish > taking reports from my > application. All users will be using the same application, from which this > report is supposed to > be printed. > maybe advisory lock is what you need? https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS
Re: Need to find the no. of connections for a database
Need to lock around 10 tables. Let me try with pg_advisory_lock(). On Thursday, 27 February, 2020, 07:39:13 pm IST, Ireneusz Pluta/wp.pl wrote: W dniu 2020-02-27 o 14:37, sivapostg...@yahoo.com pisze: > Hello, > > I'm saying isolation will not work out to my requirement. The steps. > > 1. On completion of all entries by all, say for a day. > 2. Lock, so that no one enters any other data. > 3. Create a report from the entered data. > 4. Create / Modify required entries from the values arrived in the report. [ > long process ] > 5. Once completed, commit all data. > 6. Unlock, so that other users can enter data again. Data entered will be > for another date. > Data cannot [should] not entered for the processed date. > so wouldn't pg_advisory_lock() in step 2, and pg_advisory_unlock() in step 6, make the process work in the way you expect? https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS > > > On Thursday, 27 February, 2020, 06:29:00 pm IST, Ireneusz Pluta/wp.pl > wrote: > > > W dniu 2020-02-27 o 12:35, sivapostg...@yahoo.com > <mailto:sivapostg...@yahoo.com> pisze: > > > I need to prevent other users from entering any transaction till I finish > > taking reports from my > > application. All users will be using the same application, from which this > > report is supposed to > > be printed. > > > > maybe advisory lock is what you need? > > https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS > > > >
Re: Need to find the no. of connections for a database
Then clearly I've misunderstood what advisory lock could do. We used to put locks in SQL server to avoid deadlock situations. I thought advisory lock is a similar one. [ New to Postgres ] The report is arrived from around 10 tables out of 300 tables that are in the database. Once we start this process, we need to ensure that no other user could enter any data in those 10 tables, at least for the processing period. I thought the table lock [ those 10 tables ] will ensure no entry. We have a menu like this in our application Purchase EntrySales EntrySales CancellationReport Processing report When we enter the Processing report and click process, we need to ensure that no one could enter data from Purchase Entry, Sales Entry, Sales Cancellation, etc. Couldn't understand how advisory lock could achieve this? Happiness AlwaysBKR Sivaprakash On Thursday, 27 February, 2020, 10:04:12 pm IST, Ireneusz Pluta/wp.pl wrote: W dniu 2020-02-27 o 15:26, sivapostg...@yahoo.com pisze: > Need to lock around 10 tables. Let me try with pg_advisory_lock(). I am not sure why you mention table locks at this point. Just in case: with advisory locks you lock an "application flow", not database objects. > > > On Thursday, 27 February, 2020, 07:39:13 pm IST, Ireneusz Pluta/wp.pl > wrote: > > > W dniu 2020-02-27 o 14:37, sivapostg...@yahoo.com > <mailto:sivapostg...@yahoo.com> pisze: > > Hello, > > > > I'm saying isolation will not work out to my requirement. The steps. > > > > 1. On completion of all entries by all, say for a day. > > 2. Lock, so that no one enters any other data. > > 3. Create a report from the entered data. > > 4. Create / Modify required entries from the values arrived in the report. > > [ long process ] > > 5. Once completed, commit all data. > > 6. Unlock, so that other users can enter data again. Data entered will be > > for another date. > > Data cannot [should] not entered for the processed date. > > > so wouldn't pg_advisory_lock() in step 2, and pg_advisory_unlock() in step 6, > make the process work > in the way you expect? > > https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS > > > > > > > On Thursday, 27 February, 2020, 06:29:00 pm IST, Ireneusz Pluta/wp.pl > > <mailto:ipl...@wp.pl>> wrote: > > > > > > W dniu 2020-02-27 o 12:35, sivapostg...@yahoo.com > > <mailto:sivapostg...@yahoo.com> > <mailto:sivapostg...@yahoo.com <mailto:sivapostg...@yahoo.com>> pisze: > > > > > > I need to prevent other users from entering any transaction till I finish > > > taking reports from my > > > application. All users will be using the same application, from which > > > this report is supposed to > > > be printed. > > > > > > > maybe advisory lock is what you need? > > > > https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS > > > > > > > > > >
Re: Need to find the no. of connections for a database
Since the no. of entry screens to be locked might increase with enhancement(s), the approach could be 1. On completion of all entries by all, say for a day.2. When opening the report, check whether only one user has logged in. Close the report window, if there are more than one users.3. Flag somewhere in the db, that the processing has started.4. Check at the opening event of the 'tobe locked screens', whether this flag set in point 3. If set, quit the screen.5. Create a report from the entered data. 6. Create / Modify required entries from the values arrived in the report. [ long process ]7. Once completed, commit all data. Reset the flag set in point 3.8. Ensure that no data entered for the processed period. Data entered will be for another date. Data cannot [should] not entered for the processed date. If this works out, we need to find a way to count the no. of users logged in the database. On Friday, 28 February, 2020, 11:35:48 am IST, Rob Sargent wrote: On 2/27/20 10:38 PM, rob stone wrote: > > If the menu is built from a table in your database, then when > "Processing report" starts you could set a flag (boolean) against those > items so that if anybody tried to log-in or access those items, you > could simply display a message along the lines of "Processing report is > running. Please try again later". > > When "Processing report" finishes, it just clears that flag. > > HTH, > Rob > Conversely, the OP could be asking for a way to turn "process report" to active state when no one is using the other features: each of them could set a lock/counter I guess and when all counters are zero enable process button. The notion of waiting for "all clear" from the app or the db before running reports does seem odd though. Do certain actions from the app leave the database in an inconsistent state and break the report were it run at the same time?
Re: enabling uuid-ossp in centos 7
Hello,Installed postgresql 11 in CentOS 7 through yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm yum install postgresql11 yum install postgresql11-server /usr/pgsql-11/bin/postgresql-11-setup initdb Now the commandcreate extension if not exists "uuid-ossp";gives the following error could not open extension control file "/usr/pgsql-11/share/extension/uuid-ossp.control": No such file or directorySQL state: 58P01 What to do to enable this extension? In windows, this works fine. Issue is when installing under Linux only. Happiness AlwaysBKR Sivaprakash
Re: select * from test where name like 'co_%'
Hello, What returns when I run a query like this; Select * from test where name like 'co_%'; I expect anything that starts with 'co_' and NOT 'co' only. Am I right? But I get every names that starts with 'co'. Why ? Happiness Always BKR Sivaprakash
Re: select * from test where name like 'co_%'
Thanks. Got it.
On Tuesday, 10 March, 2020, 06:30:27 pm IST, Paul Foerster
wrote:
Hi,
an underscore matches a single character, any character. You'd have to
escape it and tell the query what the escape character is if you want
it to be treated as a standard character:
db=# create table t(t text);
CREATE TABLE
db=# insert into t(t) values ('fox'), ('fo_'), ('fo_x');
INSERT 0 3
db=# select * from t;
t
--
fox
fo_
fo_x
(3 rows)
db=# select * from t where t like 'fo_%';
t
--
fox
fo_
fo_x
(3 rows)
db=# select * from t where t like 'fo\_%' escape '\';
t
--
fo_
fo_x
(2 rows)
Cheers,
Paul
On Tue, Mar 10, 2020 at 1:49 PM sivapostg...@yahoo.com
wrote:
>
> Hello,
>
> What returns when I run a query like this;
>
> Select * from test where name like 'co_%';
>
> I expect anything that starts with 'co_' and NOT 'co' only. Am I right? But
> I get every names that starts with 'co'. Why ?
>
> Happiness Always
> BKR Sivaprakash
>
Re: How to set a value when NULL
Hello,Need to set a value of Zero when the field value is NULL in trigger function. Tried with,NEW.fieldname = NULLIF(NEW.fieldname, 0) in before insert/update trigger. Looks like it's not working. I'm doing anything wrong. Happiness AlwaysBKR Sivaprakash
Re: Application Level Encryption
HFSQL - Not a so popular database allow us to encrypt and password protect every tables [ they term Tables as Files ]. Without password those tables could not read even through HFSQL management centre [ like pgadmin]. May be such a facility you refer ? Sent from Yahoo Mail on Android On Sun, Jul 5, 2020 at 5:16 PM, o1bigtenor wrote: On Sun, Jul 5, 2020 at 1:22 AM Zahir Lalani wrote: Hello Does anyone have any serious experience of implementing app level encryption with strong key management? If so would like to arrange an offline chat please Others might be interested as well. Might be useful to have specific questions if a knowledgeable individual did appear. Regards
Re: Error While reinstalling PG 11
Hello, Windows Server 2008 R2, Postgresql 11.8 We formatted our server and tried to install postgres again by setting the same old data directory. And we got the following error. There has been an error:Unknown error while running C:\Windows\System32\icards "D:\PG Data\V11" /T /Q /grant "NT AUTHORITY\NetworkService:(OI)(CI)F" The application will exit now. What settings we need to do to reinstall postgres again ? Happiness AlwaysBKR Sivaprakash
Re: Copy & Re-copy of DB
Hello, I'm from SQL Server now developing my current project using PG. In our earlier project(s), we used to copy the database from one server to another, work in another server, and re-copy it in the original server. All happened easily with just transferring the two files (mdf & ldf) to our required server. Want to replicate the same in PG. Is there any way to accomplish the same in PG ? Limitation of Backup utility. Transfer to another server was done successfully. When we want to re-copy to original server, we have to delete the original db in the original server, create a new db, restore it. Is that the only way ? or any other elegant way available ? Happiness AlwaysBKR Sivaprakash
Re: Copy & Re-copy of DB
create database is to create a new database. If we switch to new database, we need to change the new databasename in the program(s) that access this database. Is there any way to overwrite the data ? On Thursday, 21 January, 2021, 07:12:19 pm IST, Ray O'Donnell wrote: On 21/01/2021 13:13, sivapostg...@yahoo.com wrote: > Hello, > > I'm from SQL Server now developing my current project using PG. > > In our earlier project(s), we used to copy the database from one server > to another, work in another server, and re-copy it in the original > server. All happened easily with just transferring the two files (mdf > & ldf) to our required server. > > Want to replicate the same in PG. Is there any way to accomplish the > same in PG ? create database template Would that do the job? You could also use pg_dump. Ray. -- Raymond O'Donnell // Galway // Ireland r...@rodonnell.ie
Re: Copy & Re-copy of DB
So the solution to the issue will be 1. Backup a DB using PGDUMP from 1st server.2. Restore the DB in 2nd server.3. Make required changes in the 2nd server.4. Backup that DB using PGDUMP from 2nd server.5. Delete / Rename that DB in the 1st server6. Restore that DB in the 1st server.7. Work again in the 1st server. Is this the best way to carry out this process? Happiness AlwaysBKR Sivaprakash On Friday, 22 January, 2021, 12:40:05 am IST, Ken Tanzer wrote: On Thu, Jan 21, 2021 at 5:52 AM sivapostg...@yahoo.com wrote: create database is to create a new database. If we switch to new database, we need to change the new databasename in the program(s) that access this database. If you are just concerned about having the same database name, what about renaming (ALTER DATABASE ... RENAME TO) the old and/or new databases so they end up the way you want? Cheers, Ken -- AGENCY Software A Free Software data systemBy and for non-profitshttp://agency-software.org/https://demo.agency-software.org/client ken.tan...@agency-software.org(253) 245-3801 Subscribe to the mailing list tolearn more about AGENCY orfollow the discussion.
Re: Copy & Re-copy of DB
We are an ISV. I agree the risk involved in sharing the data. Still few of my customers need that facility and are accustomed to it when using SQL Server. On switch over to PG, I face this issue as a limitation. Need to find and provide a solution. For those customers, having good volume of data, we're implementing replication which resolves this issue. For smaller sized database (company(ies)), they prefer (and we too) this copy and re-copy procedure, to transfer the data between home and office. And this pandemic made this a compulsory feature, which they don't want to loose. This transfer is not a one time job, it gets repeated, which they have been doing for years. Here security is not a big concern for them. Portability is the need for them. Happiness AlwaysBKR Sivaprakash On Friday, 22 January, 2021, 09:28:13 pm IST, Rory Campbell-Lange wrote: On 22/01/21, Benedict Holland (benedict.m.holl...@gmail.com) wrote: > Sometimes it is easier to simply > replicate the existing bad process > that a team agrees to rather than making > a better process. As Alvar Aalto said in a lecture at MIT It is not by temporary building that Parthenon comes on Acropolis.
Re: Slow while inserting and retrieval (compared to SQL Server)
Hello, Using Postgres 11 in Windows Server 2012 & PowerbuilderWorking from the same machine where Postgres 11 is installed. So no chance for any network issue, I feel. No setup/config change done. Just working with all the default settings.With no data in any of the 179 tables in that database. To populate some basic data we try to insert few records (max 4 records) in few tables (around 6 tables) from one window. We feel that the insert time taken is longer than the time taken while using Sql Server. We tested almost a similar window that updated the similar table(s) in SQL server, which was faster. With Postgres database, we need to wait for a couple of seconds before the insert/update is over, which we didn't feel in Sql Server. I feel that some changes in settings might improve this speed, but with not much knowledge in Postgres I struggle to find out those setup values. Any help in improving the speed is really appreciated. Happiness AlwaysBKR Sivaprakash
Re: Slow while inserting and retrieval (compared to SQL Server)
Hello,Yes, that's what I feel. With no records in any tables, working from the same machine where PG has been installed, with only one user working, inserting few records (10 records in total, in all 6 tables) should not take this much time. I'll be inserting records from PowerBuilder applications, and how to catch the result of Explain. OR should I run all insert commands in PG Admin or so and catch those results? As I'm new to PG, any documentation/help in this direction will be useful. Happiness AlwaysBKR Sivaprakash On Wednesday, 17 February, 2021, 05:35:43 pm IST, wrote: - Mensagem original - > De: sivapostg...@yahoo.com > Para: "pgsql-general" > Enviadas: Quarta-feira, 17 de fevereiro de 2021 9:01:15 > Assunto: Re: Slow while inserting and retrieval (compared to SQL Server) > > Hello, > > Using Postgres 11 in Windows Server 2012 & Powerbuilder > Working from the same machine where Postgres 11 is installed. So no chance > for any network issue, I feel. > No setup/config change done. Just working with all the default settings. > With no data in any of the 179 tables in that database. > > To populate some basic data we try to insert few records (max 4 records) in > few tables (around 6 tables) from one window. We feel that the insert time > taken is longer than the time taken while using Sql Server. We tested almost > a similar window that updated the similar table(s) in SQL server, which was > > > faster. With Postgres database, we need to wait for a couple of seconds > before the insert/update is over, which we didn't feel in Sql Server. > > I feel that some changes in settings might improve this speed, but with not > much knowledge in Postgres I struggle to find out those setup values. > > Any help in improving the speed is really appreciated. > > Happiness Always > BKR Sivaprakash Can you run EXPLAIN (ANALYZE,BUFFERS) on the INSERT command and post the results here? Usually inserting this many records should be instantaneous.
Re: Slow while inserting and retrieval (compared to SQL Server)
We use datawindows. Datawindows will send the required DML statements to the database. And it sent in format 1 . IN start of the application, Autocommit set to True.Before update of any table(s)Autocommit is set to FalseInsert/Update/Delete recordsIf success commit else rollbackAutocommit is set to True This has been followed for decades and it's working fine with Sql server. Here we are trying to insert just 10 records spread across 6 tables, which is taking more time.. that's what we feel. The similar work in SQL Server takes much less time < as if no wait is there >. On Wednesday, 17 February, 2021, 06:48:35 pm IST, Thomas Kellerer wrote: sivapostg...@yahoo.com schrieb am 17.02.2021 um 13:01: > To populate some basic data we try to insert few records (max 4 > records) in few tables (around 6 tables) from one window. We feel > that the insert time taken is longer than the time taken while using > Sql Server. We tested almost a similar window that updated the > similar table(s) in SQL server, which was faster. With Postgres > database, we need to wait for a couple of seconds before the > insert/update is over, which we didn't feel in Sql Server. Are you doing single-row inserts like: insert into ... values (..); insert into ... values (..); insert into ... values (..); insert into ... values (..); or are you doing multi-row inserts like this: insert into ... values (..), (..), (..), (..); Typically the latter will perform much better (especially if autocommit is enabled)
Re: Slow while inserting and retrieval (compared to SQL Server)
So far no performance tuning done for sql server. It works fine for the load. Even the express edition which is free, works better. I don't think postgres will be so slow to insert such a low number of records in an empty database. I'll be preparing the required sql statements to insert those records in pgadmin and see the timings, tomorrow. On Wednesday, 17 February, 2021, 07:29:29 pm IST, Benedict Holland wrote: Sql server is a 10k dollar to 1 million dollar application (or more) that is specifically optimized for windows and had limited to no support anywhere else. Postgres is free and from my experience, comes within 5% of any other dbms. Inserting 1 row at a time with auto commit on will be a bit slow but it shouldn't be noticeable. What times are you seeing if you do this with pgadmin4 compared to sql server? Also, have you done any performance tuning for postgres server? There are many documents detailing performance tuning your servers, like you probably did, at some point, with your sql server. Thanks,Ben On Wed, Feb 17, 2021, 8:28 AM sivapostg...@yahoo.com wrote: We use datawindows. Datawindows will send the required DML statements to the database. And it sent in format 1 . IN start of the application, Autocommit set to True.Before update of any table(s)Autocommit is set to FalseInsert/Update/Delete recordsIf success commit else rollbackAutocommit is set to True This has been followed for decades and it's working fine with Sql server. Here we are trying to insert just 10 records spread across 6 tables, which is taking more time.. that's what we feel. The similar work in SQL Server takes much less time < as if no wait is there >. On Wednesday, 17 February, 2021, 06:48:35 pm IST, Thomas Kellerer wrote: sivapostg...@yahoo.com schrieb am 17.02.2021 um 13:01: > To populate some basic data we try to insert few records (max 4 > records) in few tables (around 6 tables) from one window. We feel > that the insert time taken is longer than the time taken while using > Sql Server. We tested almost a similar window that updated the > similar table(s) in SQL server, which was faster. With Postgres > database, we need to wait for a couple of seconds before the > insert/update is over, which we didn't feel in Sql Server. Are you doing single-row inserts like: insert into ... values (..); insert into ... values (..); insert into ... values (..); insert into ... values (..); or are you doing multi-row inserts like this: insert into ... values (..), (..), (..), (..); Typically the latter will perform much better (especially if autocommit is enabled)
Re: Issue in PG start
Hello, PG 11.8 in Windows 10 and currently PG 11.11 Yesterday [07th May] morning when we switched on the computer and subsequently PGAdmin, we got the message following messageFATAL: the database system is starting up I uninstalled PG and installed [v 11.11] PG with the same data directory. Fortunately it worked even when we switched off /on the computer for lunch. NO issues yesterday. But today morning [08th May], again we have the same issue and same message. The log file is with the following message only 2021-05-08 10:26:20.783 IST [7360] LOG: database system was interrupted; last known up at 2021-05-07 18:46:00 IST2021-05-08 10:28:36.159 IST [7048] FATAL: the database system is starting up2021-05-08 10:28:46.199 IST [15240] FATAL: the database system is starting up2021-05-08 10:28:56.233 IST [7036] FATAL: the database system is starting up2021-05-08 10:29:06.266 IST [5656] FATAL: the database system is starting up2021-05-08 10:29:16.299 IST [4464] FATAL: the database system is starting up2021-05-08 10:29:26.327 IST [11764] FATAL: the database system is starting up2021-05-08 10:29:36.365 IST [4248] FATAL: the database system is starting up2021-05-08 10:29:46.406 IST [14920] FATAL: the database system is starting up2021-05-08 10:29:56.429 IST [6364] FATAL: the database system is starting up2021-05-08 10:30:06.458 IST [14472] FATAL: the database system is starting up2021-05-08 10:30:16.489 IST [1372] FATAL: the database system is starting up2021-05-08 10:30:26.521 IST [4232] FATAL: the database system is starting up2021-05-08 10:30:36.547 IST [14832] FATAL: the database system is starting up2021-05-08 10:30:46.578 IST [13852] FATAL: the database system is starting up2021-05-08 10:30:56.607 IST [9720] FATAL: the database system is starting up2021-05-08 10:31:06.634 IST [12396] FATAL: the database system is starting up2021-05-08 10:31:16.693 IST [8] FATAL: the database system is starting up2021-05-08 10:31:26.722 IST [12492] FATAL: the database system is starting up2021-05-08 10:31:36.750 IST [15020] FATAL: the database system is starting up2021-05-08 10:31:46.778 IST [9684] FATAL: the database system is starting up2021-05-08 10:31:56.924 IST [15356] FATAL: the database system is starting up2021-05-08 10:32:06.946 IST [1060] FATAL: the database system is starting up2021-05-08 10:32:16.968 IST [14828] FATAL: the database system is starting up2021-05-08 10:32:27.025 IST [10544] FATAL: the database system is starting up2021-05-08 10:32:37.054 IST [816] FATAL: the database system is starting up2021-05-08 10:32:47.087 IST [14676] FATAL: the database system is starting up2021-05-08 10:32:57.141 IST [13116] FATAL: the database system is starting up2021-05-08 10:33:07.171 IST [15280] FATAL: the database system is starting up2021-05-08 10:33:17.202 IST [12972] FATAL: the database system is starting up2021-05-08 10:33:27.229 IST [4476] FATAL: the database system is starting up2021-05-08 10:33:37.259 IST [1992] FATAL: the database system is starting up2021-05-08 10:33:47.291 IST [5260] FATAL: the database system is starting up2021-05-08 10:33:57.425 IST [15040] FATAL: the database system is starting up2021-05-08 10:34:07.459 IST [5780] FATAL: the database system is starting up2021-05-08 10:34:17.487 IST [5052] FATAL: the database system is starting up2021-05-08 10:34:25.047 IST [15396] FATAL: the database system is starting up2021-05-08 10:34:25.071 IST [15424] FATAL: the database system is starting up2021-05-08 10:34:25.094 IST [15452] FATAL: the database system is starting up2021-05-08 10:34:25.118 IST [15480] FATAL: the database system is starting up2021-05-08 10:34:25.142 IST [15508] FATAL: the database system is starting up2021-05-08 10:34:25.318 IST [15548] FATAL: the database system is starting up2021-05-08 10:34:27.514 IST [15588] FATAL: the database system is starting up2021-05-08 10:34:37.534 IST [15668] FATAL: the database system is starting up2021-05-08 10:34:47.559 IST [15868] FATAL: the database system is starting up2021-05-08 10:34:57.592 IST [16256] FATAL: the database system is starting up2021-05-08 10:35:01.668 IST [16332] FATAL: the database system is starting up2021-05-08 10:35:07.623 IST [1332] FATAL: the database system is starting up2021-05-08 10:35:08.079 IST [15396] FATAL: the database system is starting up2021-05-08 10:35:17.653 IST [7316] FATAL: the database system is starting up2021-05-08 10:35:27.783 IST [16376] FATAL: the database system is starting up2021-05-08 10:35:37.825 IST [15596] FATAL: the database system is starting up2021-05-08 10:35:47.859 IST [15928] FATAL: the database system is starting up2021-05-08 10:35:57.887 IST [5544] FATAL: the database system is starting up2021-05-08 10:36:07.912 IST [2988] FATAL: the database system is starting up2021-05-08 10:36:17.946 IST [7532] FATAL: the database system is starting up2021-05-08 10:36:27.975 IST [13672] FATAL
Re: Issue in PG start
The above is the contents of the log file from 'data' folder of PG. Message in Windows Application Event2021-05-08 10:26:19.425 IST [5588] LOG: listening on IPv4 address "0.0.0.0", port 5433 On Saturday, 8 May, 2021, 11:02:06 am IST, David G. Johnston wrote: On Friday, May 7, 2021, sivapostg...@yahoo.com wrote: Hello, PG 11.8 in Windows 10 and currently PG 11.11 Yesterday [07th May] morning when we switched on the computer and subsequently PGAdmin, we got the message following messageFATAL: the database system is starting up I uninstalled PG and installed [v 11.11] PG with the same data directory. Fortunately it worked even when we switched off /on the computer for lunch. NO issues yesterday. But today morning [08th May], again we have the same issue and same message. The log file is with the following message only 2021-05-08 10:26:20.783 IST [7360] LOG: database system was interrupted; last known up at 2021-05-07 18:46:00 IST How / where to debug the issue and correct it ? To see why the server is not completing its startup cycle you need to look at the server’s log file. All you’ve shown is a client failing to establish a connection every ten seconds. David J.
Re: Issue in PG start
Or server’s log file is available elsewhere ? On Saturday, 8 May, 2021, 01:24:50 pm IST, sivapostg...@yahoo.com wrote: The above is the contents of the log file from 'data' folder of PG. Message in Windows Application Event2021-05-08 10:26:19.425 IST [5588] LOG: listening on IPv4 address "0.0.0.0", port 5433 On Saturday, 8 May, 2021, 11:02:06 am IST, David G. Johnston wrote: On Friday, May 7, 2021, sivapostg...@yahoo.com wrote: Hello, PG 11.8 in Windows 10 and currently PG 11.11 Yesterday [07th May] morning when we switched on the computer and subsequently PGAdmin, we got the message following messageFATAL: the database system is starting up I uninstalled PG and installed [v 11.11] PG with the same data directory. Fortunately it worked even when we switched off /on the computer for lunch. NO issues yesterday. But today morning [08th May], again we have the same issue and same message. The log file is with the following message only 2021-05-08 10:26:20.783 IST [7360] LOG: database system was interrupted; last known up at 2021-05-07 18:46:00 IST How / where to debug the issue and correct it ? To see why the server is not completing its startup cycle you need to look at the server’s log file. All you’ve shown is a client failing to establish a connection every ten seconds. David J.
Re: Issue in PG start
1. First why switch the computer off for lunch? It's a development machine and I'm a developer. We used to shut down every computer for lunch. We shut down the windows and not just hit the power switch. We advice and follow every users and client to shut down the windows properly and we follow it. 2. In postgresql.conf what is log_min_messages set to? Warning 3. Do you have replication set up with this server? No 4. Is Windows Postgresql a service? If so then Windows "should" shut it down cleanly when you cleanly power off the machine. Yes, Postgresql is a service and windows is shut down properly. We do shut down computers during lunch and when closing office, regularly and properly for the past 6 months [ since PG was installed ] without any issue. This is the first time that we face this issue. Need to find a solution that will be helpful if that happens in a production machine. Happiness Always BKR Sivaprakash On Saturday, 8 May, 2021, 10:24:03 pm IST, Tom Lane wrote: Adrian Klaver writes: > On 5/8/21 9:23 AM, Ron wrote: >> Is Windows Postgresql a service? If so then Windows "should" shut it >> down cleanly when you cleanly power off the machine. > The log would indicate otherwise: > >>> 2021-05-08 10:26:20.783 IST [7360] LOG: database system was >>> interrupted; last known up at 2021-05-07 18:46:00 IST > > So something is not working as it should. Yeah, that's bad news. In theory, as long as you didn't set fsync = off, Postgres should be able to recover from an unclean shutdown. In practice, that requires all the levels of the hardware and software stack to pay strict attention to write ordering requirements. It is, um, pretty common for inexpensive Windows gear to not be very careful about that. I'd say this particular machine has clearly failed the plug-pull test [1]. If the OP is in the habit of just hitting the power switch at lunchtime, I'd say he's got to stop doing that. Telling Windows to shut down will add a few seconds, but greatly reduce the risk of problems (and not only for Postgres). If he *did* tell Windows to shut down, then there's something else that needs adjustment. regards, tom lane [1] https://wiki.postgresql.org/wiki/Reliable_Writes
Re: Issue in PG start
now. Any idea of what happened and what should we do to prevent re-occurance ? Happiness Always BKR Sivaprakash On Sunday, 9 May, 2021, 07:49:57 am IST, sivapostg...@yahoo.com wrote: 1. First why switch the computer off for lunch? It's a development machine and I'm a developer. We used to shut down every computer for lunch. We shut down the windows and not just hit the power switch. We advice and follow every users and client to shut down the windows properly and we follow it. 2. In postgresql.conf what is log_min_messages set to? Warning 3. Do you have replication set up with this server? No 4. Is Windows Postgresql a service? If so then Windows "should" shut it down cleanly when you cleanly power off the machine. Yes, Postgresql is a service and windows is shut down properly. We do shut down computers during lunch and when closing office, regularly and properly for the past 6 months [ since PG was installed ] without any issue. This is the first time that we face this issue. Need to find a solution that will be helpful if that happens in a production machine. Happiness Always BKR Sivaprakash On Saturday, 8 May, 2021, 10:24:03 pm IST, Tom Lane wrote: Adrian Klaver writes: > On 5/8/21 9:23 AM, Ron wrote: >> Is Windows Postgresql a service? If so then Windows "should" shut it >> down cleanly when you cleanly power off the machine. > The log would indicate otherwise: > >>> 2021-05-08 10:26:20.783 IST [7360] LOG: database system was >>> interrupted; last known up at 2021-05-07 18:46:00 IST > > So something is not working as it should. Yeah, that's bad news. In theory, as long as you didn't set fsync = off, Postgres should be able to recover from an unclean shutdown. In practice, that requires all the levels of the hardware and software stack to pay strict attention to write ordering requirements. It is, um, pretty common for inexpensive Windows gear to not be very careful about that. I'd say this particular machine has clearly failed the plug-pull test [1]. If the OP is in the habit of just hitting the power switch at lunchtime, I'd say he's got to stop doing that. Telling Windows to shut down will add a few seconds, but greatly reduce the risk of problems (and not only for Postgres). If he *did* tell Windows to shut down, then there's something else that needs adjustment. regards, tom lane [1] https://wiki.postgresql.org/wiki/Reliable_Writes
Re. Select with where condition times out
Hello,PG V11 Select count(*) from table1Returns 10456432 Select field1, field2 from table1 where field3> '2024-07-18 12:00:00' Times out The above query was working fine for the past 2 years. Backup was taken a day back. Need to recover complete data as far as possible. Any possible way(s) to do this? BKR Sivaprakash Yahoo Mail: Search, organise, conquer
Re: Re. Select with where condition times out
Executed VACUUM FULL VERBOSEfollowed byREINDEX DATABASE dbname; It didn't increase the performance, still time out happened. VACUUM didn't find any dead rows in that particular table. Yes, the actual query and conditions were not given in my first comment. Actually where condition is not on the date field alone and the query with current date is only a sample. What I did,1. Took backup (pg_dump) of the database from the server it's running. [ Server config. Xeon Silver 4208, Windows Server 2019 Standard ]. 2. Restored in another desktop system, installing PG 11 afresh. 3. Performance was excellent. Within milliseconds I got the result. Application was run from the desktop. 4. Restored the database in the same server, as another database. Improved performance but doesn't match the performance of the desktop. Application run from the server itself. Now server got two databases with exactly the same data. Old one takes more than 15 minutes; newer one takes few seconds. Application run from the server and also from clients. In both conditions, the result is same. What else I need to do to correct this issue? I can easily replace the old database with the backup. Is that only option? Happiness Always BKR Sivaprakash On Thursday, 18 July, 2024 at 05:23:39 pm IST, Francisco Olarte wrote: On Thu, 18 Jul 2024 at 11:38, sivapostg...@yahoo.com wrote: > Hello, > PG V11 > > Select count(*) from table1 > Returns 10456432 > > Select field1, field2 from table1 where field3> '2024-07-18 12:00:00' > Times out How do you send the query / how does it time out? Is that the real query? Is table a table or a view? What does explain say? > Any possible way(s) to do this? If your client is timing out, increase timeout, if imposible you can try fetching in batches, but more detail would be needed. Suggestions to improve total time had already being given, try to decrease bloat if you have it, but AFAIK timeouts are configurable, so it may just be you have a too low timeout. If it had been working, is field3 indexed? How is the table modified? Because with a configured timeout, whit an unindexed table ( forcing a table scan ) the query may be working for years before you hit the bad spot. Also, the query includes todays date, so I doubt it has been used for years, probably "a similar one has been used for years", and probably that is not your real table ( or you have a naming problem ). Without giving real info, people cannot give you real solutions. Francisco Olarte.
Re: Re. Select with where condition times out
On Sunday, 21 July, 2024 at 12:52:22 am IST, Michael Nolan wrote: On Thu, Jul 18, 2024 at 4:38 AM sivapostg...@yahoo.com wrote: > > Hello, > PG V11 > > Select count(*) from table1 > Returns 10456432 > > Select field1, field2 from table1 where field3> '2024-07-18 12:00:00' > Times out > > The above query was working fine for the past 2 years. > > Backup was taken a day back. Need to recover complete data as far as > possible. > > Any possible way(s) to do this? > > BKR Sivaprakash > If you do a full backup, does it complete in a normal manner and the usual time? Have you tried doing a shutdown and restart of the database, or possibly rebooting the server? You may need to alter the database server settings to increase the maximum query time. Mike Nolan htf...@gmail.com 1. Full backup taken without any issue. Checked it by restoring as another database in the same server. No Issues. 2. PG Service stopped and re-started. Re-booted the server also. Same issue. 3. PG is working with default settings only, that's set during installation time. When the query was run in the restored database, in the same server machine, the query executed in a second. The same query, in original database, takes more than 15 min. BKR Sivaprakash
Re: Re. Select with where condition times out
On Saturday, 20 July, 2024 at 10:55:30 pm IST, Francisco Olarte
wrote:
Hi:
Please, avoid top posting, specially when replying to long mail with
various points,m it makes it nearly impossible to track what you are
replying to.
OK
On Sat, 20 Jul 2024 at 13:44, sivapostg...@yahoo.com
wrote:
> Executed
> VACUUM FULL VERBOSE
> followed by
> REINDEX DATABASE dbname;
As it has been already said, vacuum full implies reindex ( it
basically copies old table to a new one, including indexes, swaps
them, deletes old one ).
> It didn't increase the performance, still time out happened. VACUUM didn't
> find any dead rows in that particular table.
The no dead rows is the interesting part.
Yes no dead rows.
> Yes, the actual query and conditions were not given in my first comment.
> Actually where condition is not on the date field alone and the query with
> current date is only a sample.
Then they are worthless and harmful. Query time problems is normally
data and statistics dependent and always query dependent.
The query you posted has only two ways to be done, and few ways to be
improved. Suggestions for it will probably be harmful for other
queries.
Actual Query: select source_node_id, create_time from sym_data where table_name
= 'tx_combined_sales_header' and ((event_type = 'I' and row_data like
'"F92DD7AA237A45D99CA5741DF73EA3D1"%') or (event_type in ('U', 'D') and pk_data
like '"F92DD7AA237A45D99CA5741DF73EA3D1"')) and create_time >= '2024-07-18
01:43:32.981' order by create_time desc
> What I did,
> 1. Took backup (pg_dump) of the database from the server it's running. [
> Server config. Xeon Silver 4208, Windows Server 2019 Standard ].
> 2. Restored in another desktop system, installing PG 11 afresh.
> 3. Performance was excellent. Within milliseconds I got the result.
> Application was run from the desktop.
> 4. Restored the database in the same server, as another database. Improved
> performance but doesn't match the performance of the desktop. Application
> run from the server itself.
What you did not:
- Show your tables and indexes.
- Show your real queries.
- Tell us what "the application is" ( i.e., "psql", "a java app using
JDBC", ... )
> Now server got two databases with exactly the same data. Old one takes more
> than 15 minutes; newer one takes few seconds. Application run from the
> server and also from clients. In both conditions, the result is same.
After what has been happening, I have to ask. Do you mean ONE server
with two databases, or TWO servers with one database each? Also, what
are the especs of the server and the desktops, and the postgres
configuration on each? A misconfigured server can easily send query
time through the roof ( i.e., DB servers want real RAM, if you
configure postgres with too much mem and it swaps you can make a query
really slow )
I thought I'm clear. My bad.
2 computers were involved in total. One Xeon Server with Windows 2019 Standard
and other one is Intel i5 based Desktop with Windows 10.
I took backup (pg_dump) from windows server machine.
And restored in the same server as another database. Now we have 2 databases
with identical data in Windows Server. The actual query (given above) is taking
more than 15 min in the original database and takes a second in the restored
database.
Also I restored the database in Desktop machine also, which takes ms only.All
PG settings are set at installation, and nothing changed by us.
> What else I need to do to correct this issue?
No clue.
I have done Vacuum, Re-Index in the original database. No improvement. Anything
else that I can do to make the original database to perform just like the
restored database?
> I can easily replace the old database with the backup. Is that only option?
Ah, one clue. From the info I have in this and previous mails, that is
the only option for me. Having more info someone may have ideas, but
so far the only thing I have concluded is three databases, fast in
server, slow in server and desktop, test only. So my only options are
fast server and slow server. So my solution would be "use fast
server". As I said, maybe having more data we could suggest "analyze
that table with these parameters", or "make this index" or "rewrite
this condition in this way", but this is impossible to do with the
data you provided.
What else ?
Regards.
Francisco Olarte.
Re: Slow performance
Hello,Using PG 11.11, One PG Cluster, Windows 2019 Server Standard, Two
databases with identical data.
1. First DB: client_db
2. Second DB: client_test
Took backup (pg_dump) of first database (client_db) and restored the database
as second database (client_test).
Query:
Select a.examname, a.registrationnumber, b.studentname, d.departmentname,
e.levelname, a.subjectcode, c.subjectname, b.regular, a.semester,
a.dummynumber, p.semester as curr_sem, a.internalmark, a.externalmark,
a.result, coalesce((a.internalmark + a.externalmark),0) as total,
a.absent, a.malpractice, c.maxinternalmark, f.noofsemester,
a.examstudentstatus, Case When a.result = 'P' Then 'P' Else
Case When a.result = 'F' and a.malpractice = 'Y' and a.examstudentstatus is
null Then 'M' Else Case When a.result = 'F' and a.absent = 'Y' and
a.examstudentstatus = 'R' Then 'R.C' Else Case When a.result = 'F' and
a.absent = 'Y' and a.examstudentstatus = 'S' Then 'S.L' Else Case When
a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = 'F' Then 'N.P' Else
Case When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus =
'W' Then 'W.H' Else 'RA'
End End End End End End as res, Concat(RTrim(f.degreeawarded), ' ',
RTrim(d.departmentname)) as course, a.revaluation, m.absent as
int_abs, n.companyname, n.companydescription, m.totalmark as int_mark,
q.addressone, q.addresstwo, Case When a.semester > f.noofsemester Then
'PRIVATE' When a.semester <= f.noofsemester and a.semester in
(1,2) Then 'I - Year' When a.semester <= f.noofsemester and
a.semester in (3,4) Then 'II - Year' When a.semester <=
f.noofsemester and a.semester in (5,6) Then 'III - Year' End as studyr,
c.subjectserialno, p.regulararrear From cl_student_exam_subject a Join
cl_student_name b On b.companycode = a.companycode And
b.registrationnumber = a.registrationnumber Join cl_subject c On
c.companycode = a.companycode And c.subjectcode = a.subjectcode Join
cl_department_header d On d.departmentheaderpk = b.departmentheaderfk Join
cl_level e On e.levelpk = b.Levelfk Join cl_department_detail f On
f.departmentheaderfk = b.departmentheaderfk And f.levelfk = b.levelfk Left
Outer Join cl_student_internal_mark m On m.companycode = a.companycode And
m.registrationnumber = a.registrationnumber And m.subjectcode =
a.subjectcode And m.departmentheaderfk = b.departmentheaderfk And
m.levelfk = b.levelfk And m.Regular = b.Regular Join co_company n On
n.companycode = a.companycode Join cl_student_semester_subject p On
p.companycode = a.companycode And p.examheaderfk = a.examheaderfk And
p.subjectcode = a.subjectcode And p.registrationnumber =
a.registrationnumber And p.semester = a.semester Join co_company_branch q
On n.companycode = a.companycode Where a.companycode = '100' And
a.examheaderfk = 'BA80952CFF8F4E1C3F9F44B62ED9BF37' And (a.examstudentstatus
is null or a.examstudentstatus in ('R', 'S', 'W')) And b.departmentheaderfk
in ('04DF8BD89D0844DD4D8AA151EFB28657') And b.levelfk in
('37A9BEC2638844FFD5B1422D83E70EF3') And b.status = 'A' Order By
Concat(RTrim(f.degreeawarded), ' ', RTrim(d.departmentname)), Case
When a.semester > f.noofsemester Then 'PRIVATE' When a.semester
<= f.noofsemester and a.semester in (1,2) Then 'I - Year' When
a.semester <= f.noofsemester and a.semester in (3,4) Then 'II - Year'
When a.semester <= f.noofsemester and a.semester in (5,6) Then 'III -
Year' End, a.registrationnumber, b.regular, p.semester desc,
c.subjectserialno, Case When c.subjectcategory = 'T' Then 1 When
c.subjectcategory = 'P' Then 2 When c.subjectcategory = 'D' Then 3 When
c.subjectcategory = 'V' Then 4 When c.subjectcategory = 'J' Then 5 End,
c.ancillary, Case When Substring(a.subjectcode, 6, 1) = 'C' Then 1
When Substring(a.subjectcode, 6, 1) = 'S' Then 2 When
Substring(a.subjectcode, 6, 1) = 'A' Then 3 When
Substring(a.subjectcode, 6, 1) = 'E' Then 4 Else 5 End, a.subjectcode
Explain Analyze of DB 1 (client_db) :"Sort (cost=2862.35..2862.36 rows=1
width=1088) (actual time=451671.464..451671.495 rows=326 loops=1)"" Sort Key:
(concat(rtrim((f.degreeawarded)::text), ' ', rtrim((d.departmentname)::text))),
(CASE WHEN (a.semester > f.noofsemester) THEN 'PRIVATE'::text WHEN ((a.semester
<= f.noofsemester) AND (a.semester = ANY ('{1,2}'::integer[]))) THEN 'I -
Year'::text WHEN ((a.semester <= f.noofsemester) AND (a.semester = ANY
('{3,4}'::integer[]))) THEN 'II - Year'::text WHEN ((a.semester <=
f.noofsemester) AND (a.semester = ANY ('{5,6}'::integer[]))) THEN 'III -
Year'::text ELSE NULL::text END), a.registrationnum
Re: Slow performance
I've only one instance of PG in that server. Means only one postgresql.conf
for both databases.
On Friday, 26 July, 2024 at 11:12:34 am IST, Muhammad Ikram
wrote:
Hi,
Could you perform diff on postgresql.conf file to see whether values are same
for work_mem, shared_buffers, maintenance_work_mem and other related parameters?
Regards,Ikram
On Fri, Jul 26, 2024 at 10:31 AM sivapostg...@yahoo.com
wrote:
Hello,Using PG 11.11, One PG Cluster, Windows 2019 Server Standard, Two
databases with identical data.
1. First DB: client_db
2. Second DB: client_test
Took backup (pg_dump) of first database (client_db) and restored the database
as second database (client_test).
Query:
Select a.examname, a.registrationnumber, b.studentname, d.departmentname,
e.levelname, a.subjectcode, c.subjectname, b.regular, a.semester,
a.dummynumber, p.semester as curr_sem, a.internalmark, a.externalmark,
a.result, coalesce((a.internalmark + a.externalmark),0) as total,
a.absent, a.malpractice, c.maxinternalmark, f.noofsemester,
a.examstudentstatus, Case When a.result = 'P' Then 'P' Else
Case When a.result = 'F' and a.malpractice = 'Y' and a.examstudentstatus is
null Then 'M' Else Case When a.result = 'F' and a.absent = 'Y' and
a.examstudentstatus = 'R' Then 'R.C' Else Case When a.result = 'F' and
a.absent = 'Y' and a.examstudentstatus = 'S' Then 'S.L' Else Case When
a.result = 'F' and a.absent = 'Y' and a.examstudentstatus = 'F' Then 'N.P' Else
Case When a.result = 'F' and a.absent = 'Y' and a.examstudentstatus =
'W' Then 'W.H' Else 'RA'
End End End End End End as res, Concat(RTrim(f.degreeawarded), ' ',
RTrim(d.departmentname)) as course, a.revaluation, m.absent as
int_abs, n.companyname, n.companydescription, m.totalmark as int_mark,
q.addressone, q.addresstwo, Case When a.semester > f.noofsemester Then
'PRIVATE' When a.semester <= f.noofsemester and a.semester in
(1,2) Then 'I - Year' When a.semester <= f.noofsemester and
a.semester in (3,4) Then 'II - Year' When a.semester <=
f.noofsemester and a.semester in (5,6) Then 'III - Year' End as studyr,
c.subjectserialno, p.regulararrear From cl_student_exam_subject a Join
cl_student_name b On b.companycode = a.companycode And
b.registrationnumber = a.registrationnumber Join cl_subject c On
c.companycode = a.companycode And c.subjectcode = a.subjectcode Join
cl_department_header d On d.departmentheaderpk = b.departmentheaderfk Join
cl_level e On e.levelpk = b.Levelfk Join cl_department_detail f On
f.departmentheaderfk = b.departmentheaderfk And f.levelfk = b.levelfk Left
Outer Join cl_student_internal_mark m On m.companycode = a.companycode And
m.registrationnumber = a.registrationnumber And m.subjectcode =
a.subjectcode And m.departmentheaderfk = b.departmentheaderfk And
m.levelfk = b.levelfk And m.Regular = b.Regular Join co_company n On
n.companycode = a.companycode Join cl_student_semester_subject p On
p.companycode = a.companycode And p.examheaderfk = a.examheaderfk And
p.subjectcode = a.subjectcode And p.registrationnumber =
a.registrationnumber And p.semester = a.semester Join co_company_branch q
On n.companycode = a.companycode Where a.companycode = '100' And
a.examheaderfk = 'BA80952CFF8F4E1C3F9F44B62ED9BF37' And (a.examstudentstatus
is null or a.examstudentstatus in ('R', 'S', 'W')) And b.departmentheaderfk
in ('04DF8BD89D0844DD4D8AA151EFB28657') And b.levelfk in
('37A9BEC2638844FFD5B1422D83E70EF3') And b.status = 'A' Order By
Concat(RTrim(f.degreeawarded), ' ', RTrim(d.departmentname)), Case
When a.semester > f.noofsemester Then 'PRIVATE' When a.semester
<= f.noofsemester and a.semester in (1,2) Then 'I - Year' When
a.semester <= f.noofsemester and a.semester in (3,4) Then 'II - Year'
When a.semester <= f.noofsemester and a.semester in (5,6) Then 'III -
Year' End, a.registrationnumber, b.regular, p.semester desc,
c.subjectserialno, Case When c.subjectcategory = 'T' Then 1 When
c.subjectcategory = 'P' Then 2 When c.subjectcategory = 'D' Then 3 When
c.subjectcategory = 'V' Then 4 When c.subjectcategory = 'J' Then 5 End,
c.ancillary, Case When
