Re: configure multiple repository path in pgbackrest

You set pg1-path and pg1-port in both v1demo and v2demo. Shouldn't v2demo use pg2-path and pg2-port? On 6/5/19 2:43 PM, Pavan Kumar wrote: Hello Ron, Thank you so much for quick response. here is my configuration. [postgres@oralnx v2demo4448]$cat /etc/pgbackrest/pgbackrest.conf [v1demo

Re: delimeters psql /CSV

On 6/7/19 5:01 AM, paul.m...@lfv.se wrote: Hi, I have a problem with psql and CSV. C:\Users\an\bin>C:\Tmp\psql -h 10.211.43.22 -p 5432 -U postgres -d aatest -w  -c  "\copy public.""Bayern"" FROM 'C:\Users\an\test\Bayern.csv' WITH DELIMITER ';' CSV" Error:  extra data after expected last col

Re: Upgrade from PostgreSQL 9.6 to 11

On 6/9/19 11:36 PM, Pawan Sharma wrote: Hello All. What is the best way to upgrade from PostgreSQL 9.6 to PostgreSQL 11 instead of pg_upgrade. - Less downtime. - Approx database size are 1-3TB. If you really don't want to do pg_upgrade, then a possibility is multi-threaded pg_dump using d

Re: vacuum/reindex

On 6/14/19 2:55 PM, Rob Sargent wrote: Is reindex table redundant after vacuum(analyse,verbose)? Instead of "redundant", I'd call it "backwards", since doing a vacuum(analyse,verbose) on a freshly reindexed table seems more fruitful. -- Angular momentum makes the world go 'round.

Statistics tables not being updated anymore

Hi. v9.6.9 Statistics views like pg_stat_*_tables, pg_stat_*_indexes, pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore. Specifically, all counter fields are 0, and date fields are blank. The first thing I checked was postgresql.conf (but it hasn't been modified since

Re: Statistics tables not being updated anymore

On 7/1/19 1:07 PM, Adrian Klaver wrote: On 7/1/19 10:27 AM, Ron wrote: Hi. v9.6.9 Statistics views like pg_stat_*_tables, pg_stat_*_indexes, pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore. Specifically, all counter fields are 0, and date fields are blank.

Re: Statistics tables not being updated anymore

On 7/1/19 1:48 PM, Tom Lane wrote: Ron writes: Statistics views like pg_stat_*_tables, pg_stat_*_indexes, pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore. Specifically, all counter fields are 0, and date fields are blank. Does anything show up in the postmaste

Re: Statistics tables not being updated anymore

On 7/1/19 1:48 PM, Adrian Klaver wrote: On 7/1/19 11:24 AM, Ron wrote: On 7/1/19 1:07 PM, Adrian Klaver wrote: On 7/1/19 10:27 AM, Ron wrote: Hi. v9.6.9 Statistics views like pg_stat_*_tables, pg_stat_*_indexes, pg_statio_*_tables and pg_statio_*_indexes aren't being updated an

Re: Statistics tables not being updated anymore

On 7/1/19 2:43 PM, Adrian Klaver wrote: On 7/1/19 12:30 PM, Ron wrote: On 7/1/19 1:48 PM, Tom Lane wrote: Ron writes: Statistics views like pg_stat_*_tables, pg_stat_*_indexes, pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore. Specifically, all counter fields

Re: Statistics tables not being updated anymore

On 7/1/19 5:20 PM, Adrian Klaver wrote: On 7/1/19 1:38 PM, Ron wrote: On 7/1/19 2:43 PM, Adrian Klaver wrote: On 7/1/19 12:30 PM, Ron wrote: On 7/1/19 1:48 PM, Tom Lane wrote: Ron writes: Statistics views like pg_stat_*_tables, pg_stat_*_indexes, pg_statio_*_tables and pg_statio_*_indexes

Re: Statistics tables not being updated anymore

On 7/1/19 1:48 PM, Tom Lane wrote: Ron writes: Statistics views like pg_stat_*_tables, pg_stat_*_indexes, pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore. Specifically, all counter fields are 0, and date fields are blank. Does anything show up in the postmaste

Re: Multiple Postgrest Verisons how to set one version as default.

On 4/29/23 07:28, Gautham Raj wrote: Hi, *Problem: Having multiple versions of Postgres installed in CentOS 7. I Want to set the 9.5 version as default. Not able to access Postgres 9.5 through the terminal as well.* 1. For Command *psql --version* I'm getting 9.5 as the version. 2. For Com

Re: Can one user login in multile machine?

On 5/3/23 01:23, Wen Yi wrote: Hi team, can I use same user's information login in multile machines when connect to the postgres? Your question is a bit ambiguous, since: 1. "login in (to) multiple machines" typically refers logging in to the OS, and that has nothing to do with postgresql, an

Re: "PANIC: could not open critical system index 2662" - twice

On 5/4/23 13:10, Evgeny Morozov wrote: [snip] I'm now thinking of setting up a dedicated AWS EC2 instance just for these little DBs that get created by our automated tests. If the problem happens there as well then that would strongly point towards a bug in PostgreSQL, wouldn't it? Many other p

Re: Death postgres

On 5/6/23 07:19, Marc Millas wrote: Le sam. 6 mai 2023 à 09:46, Peter J. Holzer a écrit : On 2023-05-06 03:14:20 +0200, Marc Millas wrote: > postgres 14.2 on Linux redhat > > temp_file_limit set around 210 GB. > > a select request with 2 left join have crashed the serv

Re: Death postgres

On 5/6/23 08:52, Marc Millas wrote: Le sam. 6 mai 2023 à 15:15, Ron a écrit : [snip] If your question is about temp_file_limit, don't distract us with OOM issues. My question is how postgres can use space without caring about temp_file_limit. The oom info is kind of hint

Re: Additive backup and restore?

On 5/8/23 05:24, Age Apache wrote: I am designing a database for a web application. In the near future I will require past data for Audit, Security and Analysis purpose. But storing all that data will increase the database size. What are some well known approaches to archiving data for later us

Re: huge discrepancy between EXPLAIN cost and actual time (but the table has just been ANALYZED)

On 5/8/23 07:29, Kent Tong wrote: Hi, I have a complex query involving over 15 joins and a CTE query and it takes over 17s to complete. The output of EXPLAIN ANALYZE includes (somewhere deep inside): Index Scan using document_pkey on document document0_  (cost=0.29..8.31 rows=1 width

Re: order by

On 5/11/23 09:55, Marc Millas wrote: Thanks, I do know about index options. that table have NO (zero) indexes. If the table has no indices, then why did you write "it looks like there is something different within the *b-tree operator* class of varchar"?  After all, you only care about b-tr

Re: order by

On 5/11/23 09:29, Marc Millas wrote: Hi, I keep on investigating on the "death postgres" subject but open a new thread as I don't know if it's related to my pb. I have 2 different clusters, on 2 different machines, one is prod, the second test. Same data volumes. On prod if I do select col_a

Re: Adding SHOW CREATE TABLE

On 5/12/23 18:00, Kirk Wolak wrote: [snip] Where do we draw the lines? At other tables. Does Table DDL include all indexes? Absolutely! It should include constraints, clearly.  I would not think it should have triggers. Definitely triggers.  And foreign keys. Literally everything withi

Re: Adding SHOW CREATE TABLE

On 5/13/23 02:25, Kirk Wolak wrote: On Sat, May 13, 2023 at 1:03 AM Ron wrote: On 5/12/23 18:00, Kirk Wolak wrote: [snip] Where do we draw the lines? At other tables. Does Table DDL include all indexes? Absolutely! It should include constraints, clearly.  I

Re: stop

On 5/14/23 10:36, Tom Lane wrote: [snip] Another way is to visit the List-Unsubscribe: link that appears in the headers of every mail sent out by the postgresql mail list servers. That requires knowing about a feature which requires knowing that such features even exist.  (How many people thin

Re: Packed raster data in postgresql?

On 5/16/23 10:03, Elstermann, Mike wrote: Hello all, is it possible to store also packed raster data (e.g. jpg, lzw tiff, ...) in PostgreSQL? You can store any binary data you want in columns of type "bytea". -- Born in Arizona, moved to Babylonia.

Re: Postresql HA 2 nodes

On 5/16/23 16:30, Marcello Lorenzi wrote: Hi everyone, we're looking for the best solution for a 2-nodes cluster in HA with Postegresql 15. after some checks we are noticed about pgpool for the management of balancing and automatic failover. Can it be considered a viable and manageable solutio

Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

On 5/18/23 10:54, Stephen Frost wrote: Greetings, * Tony Xu (tony...@rubrik.com) wrote: The FAQ (copied below) mentioned that native transparent data encryption might be included in 16. Is it fair to assume that it will support database level encryption, that is, we can use two encryption keys

Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

On 5/18/23 13:02, Thorsten Glaser wrote: On Thu, 18 May 2023, Tony Xu wrote: Our use-case is for a multi-tenancy scenario - we are considering using different databases to store different customer's data, however, for Why not using multiple clusters then? Yet More Firewall Rules to get appro

Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

On 5/18/23 12:54, Rob Sargent wrote: On 5/18/23 11:49, Ron wrote: On 5/18/23 10:54, Stephen Frost wrote: Greetings, * Tony Xu (tony...@rubrik.com) wrote: The FAQ (copied below) mentioned that native transparent data encryption might be included in 16. Is it fair to assume that it will

Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

On 5/18/23 14:07, Thorsten Glaser wrote: On Thu, 18 May 2023, Ron wrote: Why not using multiple clusters then? Yet More Firewall Rules to get approved by the Security Team.  And then they balk at port 5433 because they've never heard of it. But mixing multiple customers on one clust

Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

On 5/18/23 15:56, Bruce Momjian wrote: On Thu, May 18, 2023 at 01:56:48PM -0500, Ron wrote: We need to keep costs down, too. Oracle (I think) does it at the DB level, and so does SQL Server.  Upper Management hears us say "sorry, no can do" and wonders what bunch of amateurs are

Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

+33607850334 www.mokadb.com <http://www.mokadb.com> On Thu, May 18, 2023 at 9:30 PM Adrian Klaver wrote: On 5/18/23 11:56, Ron wrote: > On 5/18/23 12:54, Rob Sargent wrote: >> On 5/18/23 11:49, Ron wrote: > We need to keep costs down, too. > > Oracle

Re: DBeaver postgres localhost access

On 5/20/23 09:09, Pedro Gonçalves wrote: Hi. Good afternoon. I’m having dificulties with localhost DBeaver postgres training account. Had access to it and changed password, that presently don’t remember. What can I do to get access again? From DBeaver I was told to address this request to P

Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

On 5/22/23 14:22, Tony Xu wrote: Thanks Christophe for the clarification. That's not quite right. A PostgreSQL cluster (in the traditional sense, which means one PostgreSQL server handling a particular endpoint) is isolated from any other clusters on the same machine. Thanks. I thi

Re: Would PostgreSQL 16 native transparent data encryption support database level encryption?

On 5/22/23 15:06, Adrian Klaver wrote: On 5/22/23 12:38, Ron wrote: On 5/22/23 14:22, Tony Xu wrote: RDS Postgresql would do the job just fine.  And since you can't get to the files (only access it via port 5432 and aws cli/web, there's no need for TDE. As I understand TDE wheth

Re: 15 pg_upgrade with -j

On 5/22/23 18:42, Tom Lane wrote: Jeff Ross writes: On 5/22/23 5:24 PM, Adrian Klaver wrote: So is the 1400G mostly in one database in the cluster? Yes, one big database with about 80 schemas and several other smaller databases so -j should help, right? AFAICT from a quick look at the code,

Re: 15 pg_upgrade with -j

On 5/23/23 12:19, Peter J. Holzer wrote: On 2023-05-22 21:10:48 -0500, Ron wrote: On 5/22/23 18:42, Tom Lane wrote: It looks like the assumption was that issuing link() requests in parallel wouldn't help much but just swamp your disk if they're all on the same filesystem. Maybe that

Re: 15 pg_upgrade with -j

On 5/23/23 13:58, Christoph Moench-Tegeder wrote: ## Ron (ronljohnso...@gmail.com): We'd never hardlink.  Eliminates the ability to return to the old system if something goes wrong. That's why you get yourself a recent XFS and use clone mode (still sticks you to the same filesystem

Re: DB migration : Sybase to Postgres

If I read your email correctly, I see two options - Apply FK constraints *after* initial data load. - Load parent data before child data (You can create the FKs ahead of time, but use the NOT VALID clause; then, after data is loaded do ALTER TABLE ... VALIDATE CONSTRAINT.) On 5/25/23 06:30, Se

Re: DB migration : Sybase to Postgres

I don't remember, to be honest.  ALTER TABLE ... VALIDATE CONSTRAINT was /really fast/, though. Having a supporting index (which Pg does /not/ automatically create) is vital, of course. On 5/25/23 23:16, Sengottaiyan T wrote: Thanks Ron. While enabling the constraint (valid const

Re: DB migration : Sybase to Postgres

On 5/26/23 08:38, Peter J. Holzer wrote: On 2023-05-25 08:10:42 -0500, Ron wrote: (You can create the FKs ahead of time, but use the NOT VALID clause; then, after data is loaded do ALTER TABLE ... VALIDATE CONSTRAINT.) I don't think this will work: | Normally, [ADD CONSTRAINT] will ca

Pg 16: will pg_dump & pg_restore be faster?

https://www.postgresql.org/about/news/postgresql-16-beta-1-released-2643/ says "PostgreSQL 16 can also improve the performance of concurrent bulk loading of data using COPY up to 300%." Since pg_dump & pg_restore use COPY (or something very similar), will the speed increase translate to hig

pg_upgrade and schema complexity...

Ran into this when upgrading from 13.11 to 15.3... The pg_restore phase failed with "ERROR: out of shared memory", and recommended that I increase max_locks_per_transaction.  Doing so let the process run to completion. It took 12.5 minutes to upgrade a 13GB instance.  Soon after, I upgraded a

Re: pg_upgrade and schema complexity...

On 6/2/23 19:58, Adrian Klaver wrote: On 6/2/23 17:44, Ron wrote: Ran into this when upgrading from 13.11 to 15.3... The pg_restore phase failed with "ERROR: out of shared memory", and recommended that I increase max_locks_per_transaction. Doing so let the process run to comple

Re: pg_upgrade and schema complexity...

On 6/2/23 21:22, Adrian Klaver wrote: On 6/2/23 18:06, Ron wrote: On 6/2/23 19:58, Adrian Klaver wrote: On 6/2/23 17:44, Ron wrote: Ran into this when upgrading from 13.11 to 15.3... The pg_restore phase failed with "ERROR: out of shared memory", and recommended that

Re: Is there any good optimization solution to improve the query efficiency?

Indices on TBL_RES.CID, TBL_RES.COD and the "join columns"? Have you vacuumed and analyzed the tables lately? Oliver's comment about first optimizing the individual subselects is also SOP. On 6/5/23 01:56, gzh wrote: Hi everyone, I'm running into some performance issues with my SQL query. The

Re: Composite type: Primary Key and validation

On 6/5/23 09:02, Laurenz Albe wrote: On Mon, 2023-06-05 at 11:49 +0200, Lorusso Domenico wrote: I've a couple of questions about composite type. Suppose this composite type: CREATE TYPE my_type AS (     user_ts_start My_start_timestamp,     user_ts_end My_end_timestamp,     db_ts_start My_sta

Re: Composite type: Primary Key and validation

Consider applying "database normalization" to the schema so that the columns are only in one table, and then pass around synthetic keys. On 6/5/23 10:06, Lorusso Domenico wrote: Thank's, you are right, I've the same doubts. A composite type is useful because I've to add all these information on

Re: How to store query result into another table using stored procedure

On 6/9/23 00:51, Rama Krishnan wrote: Hi All, I have a table like below Create table if not exists digi_card(      Digi_card_id varchar(100),     created_date timestamp,     updated_date timestamp,      status varchar(50),      reason varchar(50) ); Sample values: Insert into digi_card value

Re: Question about where to deploy the business logics for data processing

You can be sure that banks and academic research projects have different needs.  Heck, your University's class scheduling software has different needs from the research problems that you support. The bottom line is that putting all of the "business" logic in TypeORM *locks you into* using an O

Re: [Beginner Question] Will the backup wal file take too much storage space?

On 6/13/23 06:34, Francisco Olarte wrote: [snip] But if you want to restore to ANY point in time you would need a copy of the initial state of the database and ALL the wal files. Normally you do not want to do this, at most you want to restore to "any point in the last 3 days", in which case you

Re: Reproducing incorrect order with order by in a subquery

On 6/14/23 05:03, Ruslan Zakirov wrote: [snip] Anyway, yesterday I tried my simplified case on Pg latest, Pg 11 and on mysql latest. Had no luck. Either my test case is too simple or I can not find the correct distribution of data between two tables. This is when you need a prod copy... -- Bo

Re: date format

On 6/14/23 13:02, Marc Millas wrote: On Wed, Jun 14, 2023 at 7:27 PM David G. Johnston wrote: On Wed, Jun 14, 2023 at 9:42 AM Marc Millas wrote: Hi, I would like to load data from a file via file_fdw or COPY.. its a postgres 14 cluster but.. One da

Re: Question: Multiple pg clusters on one server can be reached with the standard port.

On 6/16/23 07:50, Laurenz Albe wrote: On Fri, 2023-06-16 at 12:35 +, Brainmue wrote: We want to minimise dependencies between the application and the associated PostgreSQL DB. The idea is that the application gets its DB alias and this is then used as a connection string. This way we can d

Re: Question: Multiple pg clusters on one server can be reached with the standard port.

On 6/16/23 10:19, Laurenz Albe wrote: On Fri, 2023-06-16 at 09:04 -0500, Ron wrote: On 6/16/23 07:50, Laurenz Albe wrote: On Fri, 2023-06-16 at 12:35 +, Brainmue wrote: We want to minimise dependencies between the application and the associated PostgreSQL DB. The idea is that the

Re: Question: Multiple pg clusters on one server can be reached with the standard port.

On 6/16/23 10:18, Laurenz Albe wrote: On Fri, 2023-06-16 at 14:49 +, Brainmue wrote: 16. Juni 2023 14:50, "Laurenz Albe" schrieb: On Fri, 2023-06-16 at 12:35 +, Brainmue wrote: We want to minimise dependencies between the application and the associated PostgreSQL DB. The idea is th

Re: Question: Multiple pg clusters on one server can be reached with the standard port.

On 6/16/23 10:54, Brainmue wrote: 16. Juni 2023 17:41, "Ron" schrieb: On 6/16/23 10:18, Laurenz Albe wrote: On Fri, 2023-06-16 at 14:49 +, Brainmue wrote: 16. Juni 2023 14:50, "Laurenz Albe" schrieb: On Fri, 2023-06-16 at 12:35 +, Brainmue wrote: We want to m

Re: Question: Multiple pg clusters on one server can be reached with the standard port.

On 6/16/23 11:05, Brainmue wrote: 16. Juni 2023 17:59, "Ron" schrieb: [snip] There's always The Cloud... spinning up a new AWS RDS Postgresql is fast and simple. (Costly, though.) We know that too, but our data should/must currently remain in-house on our own hardware. That

Re: Question: Multiple pg clusters on one server can be reached with the standard port.

On 6/19/23 05:33, Peter J. Holzer wrote: [snip] You cant setup firewall rules basedon dns names. firewall rules are based on ip adresses and dns resolution happens on rule creation. I dont have an example for nginx. As I remember nginx resolves dns names only for variables. So setup a variable w

Re: Question: Multiple pg clusters on one server can be reached with the standard port.

On 6/19/23 12:15, Peter J. Holzer wrote: On 2023-06-19 07:49:49 -0500, Ron wrote: On 6/19/23 05:33, Peter J. Holzer wrote: As Francisco already pointed out, this can't work with nginx either. The client resolves the alias and the TCP packets only contain the IP address, not the alias whic

Re: pg_service file questions

On 6/20/23 01:11, JUN ZHI wrote: Hi, I was scanning through the postgresql documentations when i came across this webpage: PostgreSQL: Documentation: 15: 34.17. The Connection Service File  . I am fairly new to database and i have

Re: Question: Multiple pg clusters on one server can be reached with the standard port.

On 6/20/23 09:54, Peter J. Holzer wrote: On 2023-06-19 16:09:34 -0500, Ron wrote: On 6/19/23 12:15, Peter J. Holzer wrote: On 2023-06-19 07:49:49 -0500, Ron wrote: On 6/19/23 05:33, Peter J. Holzer wrote: So (again, as Francisco already wrote) the best way is probably

Re: a really dumb password question

On 6/22/23 10:05, Martin Mueller wrote: I have a very stupid password question. I don’t know whether a postgres database on my Mac has a pass word or not. I access the database via the Aqua Data Studio frontend as the user postgres. I don’t think I ever added a password, and on the authentic

Re: bug or lacking doc hint

On 6/25/23 10:01, Marc Millas wrote: Hi, I have had a perf (++) pb with a join plan  cf the pb with join plan thread. I did simplify the thing up to when its a simple join between a 15M lines table and a 30k lines table. if I put in the on part something like table1.a=table2.b, Postgres does t

Re: bug or lacking doc hint

On 6/26/23 07:22, Marc Millas wrote: On Mon, Jun 26, 2023 at 5:47 AM Avin Kavish wrote: Sounds like the problem you are having is, the server is running out of temporary resources for the operation that users are trying to do. So according to Tom, on the postgres side, the operati

Re: typical active table count?

On 6/27/23 13:47, Jeremy Schneider wrote: On 6/27/23 9:32 AM, Ben Chobot wrote: We certainly have databases where far more than 100 tables are updated within a 10 second period. Is there a specific concern you have? Thank Ben, not a concern but I'm trying to better understand how common this m

Re: [Beginner Question] How to print the call link graph?

On 7/1/23 02:10, Wen Yi wrote: Hi community, I use the gdb to track the postgres like this: ... pq_getbyte () at pqcomm.c:980 980 in pqcomm.c (gdb)  next 985 in pqcomm.c (gdb)  next 986 in pqcomm.c (gdb)  next SocketBackend (inBuf=0x7ffc8f7e1310) at postgres.c:372 372 postgres.c

Re: function to_char(unknown) is not unique at character 8

On 7/6/23 04:19, gzh wrote: Hi, I upgraded the version of PostgreSQL from 12.6 to 12.13, when I execute the sql below , the to_char function caused the following error. ---SQL-- select TO_CHAR('100'); Isn't '100' already a character string? How do

track_activity_query_size max practical size?

We've got some Very Large Queries that take a long time.  Even setting taqs to 10KB isn't adequate, so I want to significantly bump it, but am concerned about side effects of setting it to 48KB or even 64KB. -- Born in Arizona, moved to Babylonia.

Re: track_activity_query_size max practical size?

On 7/7/23 09:51, Adrian Klaver wrote: On 7/7/23 07:42, Ron wrote: We've got some Very Large Queries that take a long time. An EXPLAIN(ANALYZE BUFFERS) would go a long way here. You can't run EXPLAIN(ANALYZE BUFFERS) if you don't have a query to run.  That's what track

Re: track_activity_query_size max practical size?

On 7/7/23 09:55, Adrian Klaver wrote: On 7/7/23 07:51, Adrian Klaver wrote: On 7/7/23 07:42, Ron wrote: We've got some Very Large Queries that take a long time. An EXPLAIN(ANALYZE BUFFERS) would go a long way here. Even setting taqs to 10KB isn't adequate, so I want to significa

Re: track_activity_query_size max practical size?

On 7/7/23 10:13, Adrian Klaver wrote: On 7/7/23 07:58, Ron wrote: On 7/7/23 09:55, Adrian Klaver wrote: On 7/7/23 07:51, Adrian Klaver wrote: On 7/7/23 07:42, Ron wrote: We've got some Very Large Queries that take a long time. An EXPLAIN(ANALYZE BUFFERS) would go a long way here.

Re: what causes new temp schemas to be created

On 7/10/23 09:20, David G. Johnston wrote: On Mon, Jul 10, 2023 at 7:18 AM Ted Toth wrote: When a temp table is created I see a pg_temp_NNN (for example pg_temp_3, pg_toast_temp_3) schemas created when/why are additional temp schemas created( pg_temp_4/pg_toast_temp_4)? Temporary

Re: pg_restore mostly idle on restoring a large number of tables

On 7/13/23 02:41, Boris Sagadin wrote: Hi, restoring a 1.5TB database with about 800k tables on i3.4xlarge AWS instace, PgSQL V12.15 on Ubuntu. Running pg_restore with -j 16, I noticed the pg_restore is busy for an hour or so with IO at 80%+ and then most of processes start idling and only

Re: Problem perhaps after upgrading to pgadmin4 7.4

On 7/13/23 06:20, Carl Erik Eriksson wrote: Query tool PGadmin on my mac If I enter a query like select count(*) from table_1I get a correct response from the server If I enter select * from table_1 I get an error message that I do not understand: Error Message:missing FROM-clause entry f

Re: Rocky Linux 9 and postgres10

On 7/15/23 11:37, Daniel Gallo wrote: Good afternoon! I am writing to ask you the following question. Can postgres10 be installed on rocky linux 9? Although I know that it is a version that has already finished its life cycle, we are in migration processes We have a system with centos 7.5 and po

Re: Reset Postgresql users password

On 7/12/23 14:28, Johnathan Tiamoh wrote: Hello, I wish to find out if there is a way to reset all users in Postgresql password to the same password at once. To the same value?? -- Born in Arizona, moved to Babylonia.

Re: Query take a long time and use no index

On 7/17/23 04:13, basti wrote: [snip] The Indexes: volkszaehler=# SELECT tablename,indexname,indexdef FROM pg_indexes WHERE tablename LIKE 'data%' ORDER BY tablename,indexname;  tablename |   indexname    | indexdef ---++---

Re: suggestion about time based partitioning and hibernate

On 7/18/23 01:18, Luca Ferrari wrote: Dear all, I'm looking for ideas here, and it could be someone already stepped into declarative partitioning of an existing database where Hibernate (a Java ORM) handles the tables. The situation is as follows: create table foo( id primary key, a_date date, .

Re: Upgrade Failure

pg_ctl is not pg_controldata. (I bet you ran "pg_ctl stop --mode=immediate".  That's not a clean shutdown.) On 7/18/23 05:26, Johnathan Tiamoh wrote: I used pg_ctl to stop the cluster. When I used the same pg_ctl to check status, it says no server is running On Tue, Jul 18, 2023 at 6:14 AM Da

Re: suggestion about time based partitioning and hibernate

On 7/20/23 10:31, Luca Ferrari wrote: On Wed, Jul 19, 2023 at 6:45 PM Alvaro Herrera wrote: Therefore I suggest to avoid doing that. Either look at some other partitioning scheme that doesn't involve adding columns to the primary key, or disregard partitioning for this table entirely. What do

Re: Effects of dropping a large table

On 7/23/23 05:27, Peter J. Holzer wrote: On 2023-07-23 06:09:03 -0400, Gus Spier wrote: Ah! Truncating a table does not entail all of WAL processes. From the documentation, "TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but

Re: \d don't print all the tables

On 7/23/23 21:25, Wen Yi wrote: Hi community, here's my shell: postgres=# \d Did not find any relations. postgres=# create schema namespace_a; CREATE SCHEMA postgres=# create schema namespace_b; CREATE SCHEMA postgres=# create table simple (name varchar); CREATE TABLE postgres=# create table nam

Re: Grant all privileges to user on a database

On 7/24/23 08:15, Kaushal Shriyan wrote: Hi, I am running postgresql15-server 15.3 on Red Hat Enterprise Linux release 8.7 (Ootpa) # rpm -qa | grep -i post postgresql15-server-15.3-2PGDG.rhel8.x86_64 postgresql15-libs-15.3-2PGDG.rhel8.x86_64 postgresql15-15.3-2PGDG.rhel8.x86_64 # $psql psql (1

Re: Grant all privileges to user on a database

On 7/24/23 09:09, Tom Lane wrote: "David G. Johnston" writes: The error message is misleading, you can’t directly create tables in a database, you must create them in a schema and the permission to do so is granted to the role on the schema, not the database. The actual server message is going

Re: password error in batch script

On 7/27/23 16:45, Adrian Klaver wrote: On 7/27/23 1:17 PM, Atul Kumar wrote: Hi, I have a password Vl=SO*CIz%A83FQF that is working fine like that in the command prompt but when it is being used in a batch file it is giving me an error of wrong password, the password is something looks like t

Re: PostgreSQL - How to login with my Linux user account

On 7/28/23 09:00, Amn Ojee Uw wrote: First of all, my gratitude to Adrian and David for taking the time to reply to my call for help; a 1k  thanks to you kids. Having stated the above, PostgreSQL provides a default database namely 'postgres'. Taking advantage of this service, I have change t

Re: impact pgbench on a physical replicated stream

On 7/28/23 07:20, Gert Cuykens wrote: Hi, I would like to pgbench the production postgres that is being physical replicated to a slave. Will I shoot myself in the foot because of the physical replication if I try pgbench on prodcution postgres. Replication or not, why are you running pgbench o

Re: Bogus temp file reporting?

231*TB* seems, to me, an unreasonably large number. On 7/27/23 02:30, kg.postgre...@olympiakos.com wrote: Hello, Is there a known issue with temp file reporting?  I have a production db that is reporting 231TB of temp space usage, which can’t be true.  temp_blks_written in pg_stat_statements

Re: Upgrading

On 7/30/23 06:53, Amn Ojee Uw wrote: Just a quick question. On my Debian 12, I installed PostgreSQL-12, I'd like to upgrade to the latest release of PostgreSQL. So, my question is, what is the best way to upgrade to the next release of PostgreSQL? To the latest release of v12 (which is 12.15

Re: Migrating MySQL DB to PostgreSQL DB.

On 7/30/23 09:01, Kaushal Shriyan wrote: Hi, I am running MySQL DB 8.0.34 on Red Hat Enterprise Linux release 8.7 (Ootpa). Is there a way to import MySQL DB along with data to PostgreSQL 14 database server? Please suggest. Thanks in advance. Depending on the size of the database, the "exot

Re: Completely Removing PostgreSQL

On 7/31/23 07:47, Amn Ojee Uw wrote: In my Debian 12, I have removed the following apps from my system by using the following commands: /*sudo apt-*//*get*//*–purge *//*remove*//*postgresql postgresql*//*-15*//*postgresql-client-common postgresql-common postgresql-contrib*//**//*sudo apt-*/

Re: PostgreSQL listens on localhost?

On 8/1/23 11:12, Frank Gunseor wrote: I have read that PostgreSQL listens on localhost is it possible to have it listen on an IP address? Of course.  "localhost" is just the default. -- Born in Arizona, moved to Babylonia.

Re: Different releases in the same server

When I want to run a specific version of psql, pg_dump. pg_basebackup, etc I explicitly use fully-qualified file names. Environment variables make that easy.  Ditto with having a different port number for each instance. (And even though it's too late for you regarding that, I *always* install

Re: org.postgresql.util.PSQLException: ERROR: invalid XML content. Huge Input lookup

On 8/3/23 21:22, Sai Teja wrote: Hi team, I am trying to migrate the data from db2 to postgreSQL in which one of the table is having XML data. For one of the file (13MB) I'm facing an error with ERROR: invalid XML content Detail: line 418061: internal error: Huge input lookup nested exception

Re: org.postgresql.util.PSQLException: ERROR: invalid XML content. Huge Input lookup

d States.1252 Please let me know if any other information is needed. Thanks & Best Regards, Sai Teja On Fri, 4 Aug, 2023, 8:03 am Ron, wrote: On 8/3/23 21:22, Sai Teja wrote: Hi team, I am trying to migrate the data from db2 to postgreSQL in which one of the table i

Re: PITR based recovery failing due to difference in max_connections

On 8/3/23 23:47, Kalit Inani wrote: Hi all, During PITR based recovery of a postgres instance, we are getting the following error - '2023-06-21 23:52:52.232 PDT [24244] FATAL:  hot standby is not possible because max_connections = 150 is a lower setting than on the master server (its value wa

Re: Backup Copy of a Production server.

On 8/7/23 00:02, KK CHN wrote: List, I am in need to copy a production PostgreSQL server  data( 1 TB)  to  an external storage( Say USB Hard Drive) and need to set up a backup server with this data dir. What is the trivial method to achieve this ?? 1. Is Sqldump an option at a production se

Re: Backup Copy of a Production server.

On 8/7/23 07:05, KK CHN wrote: On Mon, Aug 7, 2023 at 10:49 AM Ron wrote: On 8/7/23 00:02, KK CHN wrote: List, I am in need to copy a production PostgreSQL server  data( 1 TB)  to  an external storage( Say USB Hard Drive) and need to set up a backup server with this

Re: DB Server slow down & hang during Peak hours of Usage

On 8/7/23 08:41, KK CHN wrote: List , *Description:* Maintaining a DB Server Postgres and with a lot of read writes to this Server( virtual machine running on  ESXi 7 with CentOS 7) . ( I am not sure how to get the read / write counts or required IOPS or any other parameters for you. If  yo

Re: PostgreSQL 14.8 - server fails to start even though all transaction logs with base backup are restored

"26.3.3.2. Making An Exclusive Low-Level Backup Note The exclusive backup method is deprecated and should be avoided. Prior toPostgreSQL9.6, this was the only low-level method available, but it is now recommended that all users upgrade their scripts to use non-exclusive backups." Use

<    4   5   6   7   8   9   10   11   12   13   >