Facing issue in using special characters
Hi all, Facing issue in using special characters. We are trying to insert records to a remote Postgres Server and our application not able to perform this because of errors. It seems that issue is because of the special characters that has been used in one of the field of a row. Regards Tarkeshwar
integrate Postgres Users Authentication with our own LDAP Server
Hi all, We would need to integrate Postgres Users Authentication with our own LDAP Server. Basically as of now we are able to login to Postgress DB with a user/password credential. [cid:image001.png@01D50650.D807AE30] These user objects are the part of Postgres DB server. Now we want that these users should be authenticated by LDAP server. We would want the authentication to be done with LDAP, so basically the user credentials should be store in LDAP server Can you mention the prescribed steps in Postgres needed for this integration with LDAP Server? Regards Tarkeshwar
RE: integrate Postgres Users Authentication with our own LDAP Server
We want to setup ldap authentication in pg_hba.conf, for Postgresql users(other than postgres super user). We are getting issue with special characters by following steps given in postgres documentation. It is not accepting any special characters as special characters are mandatory in our use case. Can you please help us or have you any steps by which we can configure any postgres with LDAP? -Original Message- From: Laurenz Albe Sent: Thursday, May 9, 2019 12:12 PM To: M Tarkeshwar Rao ; pgsql-general ; 'postgres-disc...@mailman.lmera.ericsson.se' ; 'pgsql-gene...@postgresql.org' ; pgsql-performa...@postgresql.org; pgsql-hack...@postgresql.org; 'pgsql-hackers-ow...@postgresql.org' ; Aashish Nagpaul Subject: Re: integrate Postgres Users Authentication with our own LDAP Server On Thu, 2019-05-09 at 04:51 +, M Tarkeshwar Rao wrote: > We would need to integrate Postgres Users Authentication with our own LDAP > Server. > > Basically as of now we are able to login to Postgress DB with a user/password > credential. > > [roles "pg_signal_backend" and "postgres"] > > These user objects are the part of Postgres DB server. Now we want that these > users should be authenticated by LDAP server. > We would want the authentication to be done with LDAP, so basically > the user credentials should be store in LDAP server > > Can you mention the prescribed steps in Postgres needed for this integration > with LDAP Server? LDAP authentication is well documented: https://www.postgresql.org/docs/current/auth-ldap.html But I don't think you are on the right track. "pg_signal_backend" cannot login, it is a role to which you add a login user to give it certain privileges. So you don't need to authenticate the role. "postgres" is the installation superuser. If security is important for you, you won't set a password for that user and you won't allow remote logins with that user. But for your application users LDAP authentication is a fine thing, and not hard to set up if you know a little bit about LDAP. Yours, Laurenz Albe -- Cybertec | https://protect2.fireeye.com/url?k=4f372c5d-13a52101-4f376cc6-0cc47ad93d46-aed009fdc0b3e18f&u=https://www.cybertec-postgresql.com/
Can you please tell us how set this prefetch attribute in following lines.
Hi all,
How to fetch certain number of tuples from a postgres table.
Same I am doing in oracle using following lines by setting prefetch attribute.
For oracle
// Prepare query
if( OCIStmtPrepare( myOciStatement, myOciError, (text *)aSqlStatement,
// Get statement type
OCIAttrGet( (void *)myOciStatement, OCI_HTYPE_STMT, &statement_type, 0,
OCI_ATTR_STMT_TYPE, myOciError );
// Set prefetch count
OCIAttrSet( myOciStatement, OCI_HTYPE_STMT, &prefetch, 0,
OCI_ATTR_PREFETCH_ROWS, myOciError );
// Execute query
status = OCIStmtExecute( myOciServerCtx, myOciStatement, myOciError, iters, 0,
NULL, NULL, OCI_DEFAULT );
For Postgres
Can you please tell us how set this prefetch attribute in following lines. Is
PQexec returns all the rows from the table?
mySqlResultsPG = PQexec(connection, aSqlStatement);
if((PQresultStatus(mySqlResultsPG) == PGRES_FATAL_ERROR ) ||
(PQstatus(connection) != CONNECTION_OK)){}
if ((PQresultStatus(mySqlResultsPG) == PGRES_COMMAND_OK) ||
(PQresultStatus(mySqlResultsPG) == PGRES_TUPLES_OK))
{
myNumColumns = PQnfields(mySqlResultsPG);
myTotalNumberOfRowsInQueryResult = PQntuples(mySqlResultsPG);
myCurrentRowNum = 0 ;
}
Regards
Tarkeshwar
RE: Autovacuum not functioning for large tables but it is working for few other small tables.
Hi all, We have facing some discrepancy in Postgresql database related to the autovacuum functionality. By default autovacuum was enable on Postgres which is used to remove the dead tuples from the database. We have observed autovaccum cleaning dead rows from table_A but same was not functioning correctly for table_B which have a large size(100+GB) in comparision to table_A. All the threshold level requirements for autovacuum was meet and there are about Million’s of dead tuples but autovacuum was unable to clear them, which cause performance issue on production server. Is autovacuum not working against large sized tables or Is there any parameters which need to set to make autovacuum functioning? Any suggestions? Regards Tarkeshwar
RE: Autovacuum not functioning for large tables but it is working for few other small tables.
Hi all, As we know, the VACUUM VERBOSE output has a lot of dependencies from production end and is indefinite as of now. We don’t have any clue till now on why exactly the auto-vacuum is not working for the table. So we need to have a work around to move ahead for the time being. Can you please suggest any workaround so that we can resolve the issue or any other way by which we can avoid this situation? Regards Tarkeshwar -Original Message- From: Tomas Vondra Sent: Thursday, December 17, 2020 7:16 AM To: M Tarkeshwar Rao ; pgsql-performa...@postgresql.org Cc: Neeraj Gupta G ; Atul Parashar ; Shishir Singh ; Ankit Sharma Subject: Re: Autovacuum not functioning for large tables but it is working for few other small tables. On 12/16/20 12:55 PM, M Tarkeshwar Rao wrote: > Hi all, > > We have facing some discrepancy in Postgresql database related to the > autovacuum functionality. > > By default autovacuum was enable on Postgres which is used to remove > the dead tuples from the database. > > We have observed autovaccum cleaning dead rows from *table_A* but same > was not functioning correctly for *table_B* which have a large > size(100+GB) in comparision to table_A. > > All the threshold level requirements for autovacuum was meet and there > are about Million’s of dead tuples but autovacuum was unable to clear > them, which cause performance issue on production server. > > Is autovacuum not working against large sized tables or Is there any > parameters which need to set to make autovacuum functioning? > No, autovacuum should work for tables with any size. The most likely explanation is that the rows in the large table were deleted more recently and there is a long-running transaction blocking the cleanup. Or maybe not, hard to say with the info you provided. A couple suggestions: 1) enable logging for autovacuum by setting log_autovacuum_min_duration = 10ms (or similar low value) 2) check that the autovacuum is actually executed on the large table (there's last_autovacuum in pg_stat_all_tables) 3) try running VACUUM VERBOSE on the large table, it may tell you that the rows can't be cleaned up yet. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
RE: Need information on how MM frees up disk space (vaccum) after scheduled DB cleanup by BGwCronScript/BGwLogCleaner
Hi, We have got the result of the VACUUM (VERBOSE) as suggested, please find the output as following & suggest further. But please note that this was done on an non production server where uncleaned data was there, although no dead tuples as it doesn’t run any configuration at present. However I can see it’s giving some error related to “stopping truncate” due to some lock conflict. EMMPR01:~# psql -d postDb1 -p 5492 -h 101.103.109.99 mmsuper Password for user mmsuper: psql (9.4.9) Type "help" for help. postDb1# \dt+ List of relations Schema | Name | Type | Owner |Size| Description -+--+---+-++- Schema1 | auditlogentry| table | super | 0 bytes| Schema1 | audittraillogentry | table | super | 163 GB | Schema1 | audittraillogentry_temp_join | table | super | 8192 bytes | Schema1 | cdrdetails | table | super | 909 MB | Schema1 | cdrlogentry | table | super | 8192 bytes | Schema1 | consolidatorlogentry | table | super | 24 kB | Schema1 | datalostchecklog | table | super | 0 bytes| Schema1 | eventlogentry| table | super | 56 kB | Schema1 | fileddtable_file | table | super | 0 bytes| Schema1 | filescksumcollected | table | super | 27 MB | Schema1 | filescollected | table | super | 0 bytes| Schema1 | inserviceperformance | table | super | 4552 kB| Schema1 | iostatlogentry | table | super | 0 bytes| Schema1 | loggedalarmentry | table | super | 21 MB | Schema1 | matchinglogentry | table | super | 8192 bytes | Schema1 | nrtrde_nerfile | table | super | 8192 bytes | Schema1 | nrtrde_tmp_nrin | table | super | 0 bytes| Schema1 | prstatlogentry | table | super | 0 bytes| Schema1 | statisticlogentry| table | super | 4400 kB| Schema1 | statisticupgradehistory | table | super | 40 kB | Schema1 | tpmcdrlog| table | super | 0 bytes| Schema1 | upgradehistory | table | super | 40 kB | Schema1 | vmstatlogentry | table | super | 0 bytes| (23 rows) postDb1# select * from audittraillogentry order by outtime ASC limit 5; event | innodeid | innodename | sourceid | intime | outnodeid | outnodename | destinationid | outtime | bytes | cdrs | tableindex | noofsubfilesinfile | rec ordsequencenumberlist ---+--++--++---+-+---+-+---+--+++ -- (0 rows) postDb1# VACUUM (VERBOSE) audittraillogentry; INFO: vacuuming "mmsuper.audittraillogentry" INFO: scanned index "audittraillogentry_pkey" to remove 946137 row versions DETAIL: CPU 11.46s/2.92u sec elapsed 40.43 sec. INFO: scanned index "audit_intime_index" to remove 946137 row versions DETAIL: CPU 18.46s/4.57u sec elapsed 60.16 sec. INFO: scanned index "audit_outtime_index" to remove 946137 row versions DETAIL: CPU 18.28s/4.53u sec elapsed 56.35 sec. INFO: scanned index "audit_sourceid_index" to remove 946137 row versions DETAIL: CPU 52.15s/12.12u sec elapsed 176.57 sec. INFO: scanned index "audit_destid_index" to remove 946137 row versions DETAIL: CPU 46.18s/11.21u sec elapsed 163.85 sec. INFO: "audittraillogentry": removed 946137 row versions in 33096 pages DETAIL: CPU 2.02s/0.54u sec elapsed 18.75 sec. INFO: index "audittraillogentry_pkey" now contains 0 row versions in 815195 pages DETAIL: 946137 index row versions were removed. 815155 index pages have been deleted, 801425 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.10 sec. INFO: index "audit_intime_index" now contains 0 row versions in 1274980 pages DETAIL: 946137 index row versions were removed. 1274868 index pages have been deleted, 1262921 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.07 sec. INFO: index "audit_outtime_index" now contains 0 row versions in 1288204 pages DETAIL: 946137 index row versions were removed. 1288086 index pages have been deleted, 1276659 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.07 sec. INFO: index "audit_sourceid_index" now contains 0 row versions in 3711812 pages DETAIL: 946137 index row versions were removed. 3711581 index pages have been deleted, 3700051 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.02 sec. INFO: index "audit_destid_index" now contains 0 row versions in 3234747 pages DETAIL: 946137 index row versions were removed. 3234422 index pages have been deleted, 3216227 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "audittraillogentry": found 291165 removable, 0 nonremovable row versions in 137466 out of 21356455 pages DETAIL: 0 dead row versions cannot be removed
RE: Autovacuum not functioning for large tables but it is working for few other small tables.
Hi, Please find the Vacuum(verbose) output. Can you please suggest what is the reason? How can we avoid these scenarios? The customer tried to run the VACUUM(verbose) last night, but it was running continuously for 5 hours without any visible progress. So they had to abort it as it was going to exhaust their maintenance window. db_Server14=# VACUUM (VERBOSE) audittraillogentry; INFO: vacuuming "mmsuper.audittraillogentry" INFO: scanned index "audittraillogentry_pkey" to remove 11184539 row versions DETAIL: CPU 25.24s/49.11u sec elapsed 81.33 sec INFO: scanned index "audit_intime_index" to remove 11184539 row versions DETAIL: CPU 23.27s/59.28u sec elapsed 88.63 sec INFO: scanned index "audit_outtime_index" to remove 11184539 row versions DETAIL: CPU 27.02s/55.10u sec elapsed 92.04 sec INFO: scanned index "audit_sourceid_index" to remove 11184539 row versions DETAIL: CPU 110.81s/72.29u sec elapsed 260.71 sec INFO: scanned index "audit_destid_index" to remove 11184539 row versions DETAIL: CPU 100.49s/87.03u sec elapsed 265.00 sec INFO: "audittraillogentry": removed 11184539 row versions in 247622 pages DETAIL: CPU 3.23s/0.89u sec elapsed 6.64 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184545 row versions DETAIL: CPU 25.73s/45.72u sec elapsed 86.59 sec INFO: scanned index "audit_intime_index" to remove 11184545 row versions DETAIL: CPU 34.65s/56.52u sec elapsed 113.52 sec INFO: scanned index "audit_outtime_index" to remove 11184545 row versions DETAIL: CPU 35.55s/61.96u sec elapsed 113.89 sec INFO: scanned index "audit_sourceid_index" to remove 11184545 row versions DETAIL: CPU 120.60s/75.17u sec elapsed 286.78 sec INFO: scanned index "audit_destid_index" to remove 11184545 row versions DETAIL: CPU 111.87s/93.74u sec elapsed 295.05 sec INFO: "audittraillogentry": removed 11184545 row versions in 1243407 pages DETAIL: CPU 20.35s/6.45u sec elapsed 71.61 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184547 row versions DETAIL: CPU 21.84s/43.36u sec elapsed 71.72 sec INFO: scanned index "audit_intime_index" to remove 11184547 row versions DETAIL: CPU 33.37s/57.07u sec elapsed 99.50 sec INFO: scanned index "audit_outtime_index" to remove 11184547 row versions DETAIL: CPU 35.08s/60.08u sec elapsed 110.08 sec INFO: scanned index "audit_sourceid_index" to remove 11184547 row versions DETAIL: CPU 117.72s/72.75u sec elapsed 256.31 sec INFO: scanned index "audit_destid_index" to remove 11184547 row versions DETAIL: CPU 103.46s/77.43u sec elapsed 247.23 sec INFO: "audittraillogentry": removed 11184547 row versions in 268543 pages DETAIL: CPU 4.36s/1.35u sec elapsed 9.61 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184521 row versions DETAIL: CPU 26.64s/45.46u sec elapsed 80.51 sec INFO: scanned index "audit_intime_index" to remove 11184521 row versions DETAIL: CPU 35.05s/59.11u sec elapsed 111.23 sec INFO: scanned index "audit_outtime_index" to remove 11184521 row versions DETAIL: CPU 32.98s/56.41u sec elapsed 105.93 sec INFO: scanned index "audit_sourceid_index" to remove 11184521 row versions DETAIL: CPU 117.13s/71.14u sec elapsed 254.33 sec INFO: scanned index "audit_destid_index" to remove 11184521 row versions DETAIL: CPU 99.93s/81.77u sec elapsed 241.83 sec INFO: "audittraillogentry": removed 11184521 row versions in 268593 pages DETAIL: CPU 3.49s/1.14u sec elapsed 6.87 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184534 row versions DETAIL: CPU 22.73s/42.41u sec elapsed 69.12 sec INFO: scanned index "audit_intime_index" to remove 11184534 row versions DETAIL: CPU 36.78s/68.04u sec elapsed 121.60 sec INFO: scanned index "audit_outtime_index" to remove 11184534 row versions DETAIL: CPU 31.11s/52.88u sec elapsed 93.93 sec INFO: scanned index "audit_sourceid_index" to remove 11184534 row versions DETAIL: CPU 117.95s/72.65u sec elapsed 247.44 sec INFO: scanned index "audit_destid_index" to remove 11184534 row versions DETAIL: CPU 104.25s/82.63u sec elapsed 248.43 sec INFO: "audittraillogentry": removed 11184534 row versions in 268598 pages DETAIL: CPU 3.74s/1.17u sec elapsed 9.45 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184546 row versions DETAIL: CPU 21.24s/40.72u sec elapsed 68.78 sec INFO: scanned index "audit_intime_index" to remove 11184546 row versions DETAIL: CPU 34.29s/56.72u sec elapsed 99.63 sec INFO: scanned index "audit_outtime_index" to remove 11184546 row versions DETAIL: CPU 33.83s/60.99u sec elapsed 105.22 sec INFO: scanned index "audit_sourceid_index" to remove 11184546 row versions DETAIL: CPU 114.26s/70.11u sec elapsed 239.56 sec INFO: scanned index "audit_destid_index" to remove 11184546 row versions DETAIL: CPU 100.73s/73.28u sec elapsed 228.37 sec INFO: "audittraillogentry": removed 11184546 row versions in 268538 pages DETAIL: CPU 3.80s/1.18u sec elapsed 7.79 sec INFO: scanned index "audittraillogentry_pkey" to remove 11184523 ro
