Re: Trying to restore a PostgreSQL-9.6 database from an old complete dump and/or a up-to-date just base directory and other rescued files

al writes: > Hello. I'm trying to restore/rescue a database from some that I have: >* I have all the recent files in PGDATA/base > (/var/lib/postgresql/9.6/main/base/), but I have not the complete > /var/lib/postgresql/9.6/main/ > * I have all files from an old backup (and not much

RE: [EXTERNAL]: Re: pglogical install errors openSUSE Leap 42.1

FYI, I was able to get it to work with the following steps: # zypper update curl openssl # zypper install gcc openssl-devel krb5-devel # zypper install postgresql94-9.4.25-61.1.x86_64 Then needed to download the 2.2.0 tarball from 2ndQuadrant as the newer versions would not compile: # bzip2 -d

Re: Error “cache lookup failed for function”

Albrecht =?iso-8859-1?b?RHJl3w==?= writes: > OK, but after fully stopping the daemon via systemctl (which of course > disconnects all clients) and re-starting it, the cache is empty, isn't it? > So the client after re-connecting /should/ find the proper function? In my > case the full

Re: ERROR: too many dynamic shared memory segments

Hello, I also get high amount of "too many dynamic shared memory segments" errors. Upgraded Postgres version to 12.2, but that did not help. Server has 64GB Ram/16 CPU. Postgres params: "max_connections":500, "shared_buffers":"16GB", "effective_cache_size":"48GB",

Re: Error “cache lookup failed for function”

Albrecht =?iso-8859-1?b?RHJl3w==?= writes: > I tried to update the running server by executing the following SQL update > script using psql: > ---8<--- > BEGIN; > -- add a column to an existing table > -- add a new table > -- add several db functions

Re: Error “cache lookup failed for function”

Am 20.02.20 19:32 schrieb(en) Tom Lane: This is, actually, not very surprising. You dropped the old function while clients were using it. The new function is a completely unrelated object, even if it happens to have the same name. Yes, I agree that this was not a too clever approach… It

Re: Error “cache lookup failed for function”

I wrote: > It does seem a bit annoying that something in plpgsql is apparently > doing a fresh catalog lookup to find information that likely was > already cached at the start of function execution. But I think that's > a performance deficiency, not a bug per se. I reproduced this issue locally

Error “cache lookup failed for function”

Hi all, On a Debian Buster/64 Bit I run Postgres 11 (apt-cache info): Package: postgresql-11 Version: 11.7-0+deb10u1 Maintainer: Debian PostgreSQL Maintainers I tried to update the running server by executing the following SQL update script using psql:

Re: Error “cache lookup failed for function”

Given the explicit begin before drop/create, this is a bit of an unexpected gotcha to have any impact on other transactions. Are there other known limitations of DDL in a transaction?

before insert for each row trigger on upsert

I'm a little confused why the before insert trigger fires al all but since it does is there a way to know that an update will occur? Basically I don't want the trigger affect the row on update. Ted

Re: Trying to restore a PostgreSQL-9.6 database from an old complete dump and/or a up-to-date just base directory and other rescued files

On 20/2/20 15:47, Tom Lane wrote: > > Unfortunately, not having an up-to-date copy of the xlog info is > practically fatal to your hopes of getting back consistent data. > [...] > Ok, so if the only way to restore the data is getting the content in pg_xlog directory I'm looking for it. I went

How to fix 0xC0000005 exception in Postgres 9.0

Hi! Every time when user tries to log on from same virtual computer where Postgreql resides, Postgres terminates with 0xC005 exception. Log is below. About 65 users are accessing this server over VPN. They can use server normally. Logging from localhost using pgadmin 4 with user

Re: before insert for each row trigger on upsert

On 2/20/20 12:17 PM, Ted Toth wrote: I'm a little confused why the before insert trigger fires al all but since it does is  there a way to know that an update will occur? Because ON CONFLICT DO UPDATE is part of an INSERT command. Basically I don't want the trigger affect the row on update.

Re: before insert for each row trigger on upsert

On Thu, Feb 20, 2020 at 2:32 PM Adrian Klaver wrote: > On 2/20/20 12:17 PM, Ted Toth wrote: > > I'm a little confused why the before insert trigger fires al all but > > since it does is there a way to know that an update will occur? > > Because ON CONFLICT DO UPDATE is part of an INSERT

Re: before insert for each row trigger on upsert

On 2/20/20 1:03 PM, Ted Toth wrote: On Thu, Feb 20, 2020 at 2:32 PM Adrian Klaver > wrote: On 2/20/20 12:17 PM, Ted Toth wrote: > I'm a little confused why the before insert trigger fires al all but > since it does is  there a way to know that

Re: How to fix 0xC0000005 exception in Postgres 9.0

Hi! Upgrade to a version of Postgres that is not 5 years past EOL? Not possible at moment because requires lot of testing not to break existing applications. Planned in future. Andrus.

Re: How to fix 0xC0000005 exception in Postgres 9.0

Hi! Given this is localhost connection, start looking at firewall or AV. Windows firewall is turned off. It does not affect to local connections in same computer. I turned windows antivirus off but problem persists. Andrus.

Re: Error “cache lookup failed for function”

On 2/20/20 11:28 AM, Albrecht Dreß wrote: Am 20.02.20 19:32 schrieb(en) Tom Lane: This is, actually, not very surprising.  You dropped the old function while clients were using it.  The new function is a completely unrelated object, even if it happens to have the same name. Yes, I agree that

Re: How to fix 0xC0000005 exception in Postgres 9.0

This error is really common and is caused by windows via UAC, SFC, Firewarll, AV etc.. for it spontaneously to appear means system config change happened or update occurred. Common sources windows updates, anti-viruses changes, firewall changes. If its not the above 4 figuring out what

Re: How to fix 0xC0000005 exception in Postgres 9.0

On 2/20/20 1:27 PM, Andrus wrote: Hi! Upgrade to a version of Postgres that is not 5 years past EOL? Not possible at moment because requires lot of testing not to break existing applications. Planned in future. Alright. Then what changes were done recently, in particular ODBC-related.

Re: How to fix 0xC0000005 exception in Postgres 9.0

On 2020-Feb-20, Andrus wrote: > > Upgrade to a version of Postgres that is not 5 years past EOL? > > Not possible at moment because requires lot of testing not to break existing > applications. > Planned in future. Applications are already broken, since they can't connect, right? Seems like a

Re: How to fix 0xC0000005 exception in Postgres 9.0

On 2/20/20 12:35 PM, Andrus wrote: Hi! Every time when user tries to log on from same virtual computer where Postgreql resides, Postgres terminates with 0xC005 exception. Log is below. About 65 users are accessing this server over VPN. They can use server normally. Logging from

spectrogram index?

Hello, please can you help me? I like to build a little service aka Shazam. Does exists audio spectrogram / fuzzy matching index or library API? Thank you. Roberto Della Pasqua www.dellapasqua.com

Re: How to fix 0xC0000005 exception in Postgres 9.0

having chased this error several times over the last 20 years, if its not windows update, AV update, firewall config change, UAC or a driver update it is nearly impossible to to figure out what is causing it. One can try to run sysinternal app such as process explorer or depends to look over the

Re: before insert for each row trigger on upsert

Ted Toth writes: > I think you are right in that the trigger doesn't change the row being > updated (which is good) but it also updates another table and that's what > I'd actually like to avoid on update. Probably need to put that in an AFTER trigger, then. By definition, BEFORE triggers don't

Re: How to fix 0xC0000005 exception in Postgres 9.0

On 2/20/20 2:29 PM, Andrus wrote: Hi! >Then what changes were done recently, in particular ODBC-related. Same psqlODBC driver 09.00.0101 has worked 10 years. Previously I logged into this server long time ago and then it worked. Windows update history shows lot of updates all called Security

Re: How to fix 0xC0000005 exception in Postgres 9.0

On 2/20/20 4:35 PM, Adrian Klaver wrote: On 2/20/20 2:29 PM, Andrus wrote: Hi!  >Then what changes were done recently, in particular ODBC-related. Same psqlODBC driver 09.00.0101 has worked 10 years. Previously I logged into this server long time ago and then it worked. Windows update history

Re: How to fix 0xC0000005 exception in Postgres 9.0

On 2/20/20 2:42 PM, Alvaro Herrera wrote: On 2020-Feb-20, Andrus wrote: Upgrade to a version of Postgres that is not 5 years past EOL? Not possible at moment because requires lot of testing not to break existing applications. Planned in future. Applications are already broken, since they

Re: How to fix 0xC0000005 exception in Postgres 9.0

On 2/20/20 4:50 PM, Adrian Klaver wrote: On 2/20/20 2:48 PM, Ron wrote: On 2/20/20 4:35 PM, Adrian Klaver wrote: On 2/20/20 2:29 PM, Andrus wrote: Hi!  >Then what changes were done recently, in particular ODBC-related. Same psqlODBC driver 09.00.0101 has worked 10 years. Previously I logged

Re: How to fix 0xC0000005 exception in Postgres 9.0

On 2/20/20 2:42 PM, Alvaro Herrera wrote: On 2020-Feb-20, Andrus wrote: Upgrade to a version of Postgres that is not 5 years past EOL? Not possible at moment because requires lot of testing not to break existing applications. Planned in future. Applications are already broken, since they

Re: How to fix 0xC0000005 exception in Postgres 9.0

On 2/20/20 2:48 PM, Ron wrote: On 2/20/20 4:35 PM, Adrian Klaver wrote: On 2/20/20 2:29 PM, Andrus wrote: Hi!  >Then what changes were done recently, in particular ODBC-related. Same psqlODBC driver 09.00.0101 has worked 10 years. Previously I logged into this server long time ago and then it

Can we have multiple tablespaces with in a database.

Hi Team, Can we have multiple tablespaces with in a database in postgres? Can we have a table on different tablespace same as Oracle? Thanks,

RE: Can we have multiple tablespaces with in a database.

You mean we can have only single default tablespace for a database but the database objects can be created on different-2 tablespaces? Can you please share the Doc URL for your suggestions given in trail mail. Please correct me. -Original Message- From: Christophe Pettus Sent:

Re: Can we have multiple tablespaces with in a database.

On 2/20/20 11:46 PM, David G. Johnston wrote: Please pick a single list to post to.  Performance seems like the unnecessary one here. On Thu, Feb 20, 2020 at 10:34 PM Daulat Ram > wrote: Can we have multiple tablespaces with in a database in postgres?

RE: Can we have multiple tablespaces with in a database.

Hi Amul , Please share the examples how we can create no. of tablespaces for a single database and how we can use them. As I know we can create database on tablespace 1. CREATE TABLESPACE conn_tbs OWNER enterprisedb LOCATION '/mnt/pgdatatest/test/pgdata/conn_tbs'; 2. Create database test

RE: Can we have multiple tablespaces with in a database.

That will be great if you share any doc where it’s mentioned that we can’t use multiple tablespace for a single database. I have to assist my Dev team regarding tablespaces. Also , what are the differences between Oracle and Postgres Tablespacs? Thanks, From: amul sul Sent: Friday,

Re: Can we have multiple tablespaces with in a database.

On Fri, Feb 21, 2020 at 11:04 AM Daulat Ram wrote: > Hi Team, > > > > Can we have multiple tablespaces with in a database in postgres? > > Yes. > Can we have a table on different tablespace same as Oracle? > Yes -- specify TABLESPACE option while creating that table. Regards, Amul

Re: Can we have multiple tablespaces with in a database.

Please pick a single list to post to. Performance seems like the unnecessary one here. On Thu, Feb 20, 2020 at 10:34 PM Daulat Ram wrote: > Can we have multiple tablespaces with in a database in postgres? > I fell as if I'm missing something in your question given the presence of the "CREATE

Re: Can we have multiple tablespaces with in a database.

> On Feb 20, 2020, at 22:34, Daulat Ram wrote: > > You mean we can have only single default tablespace for a database but the > database objects can be created on different-2 tablespaces? Yes. > Can you please share the Doc URL for your suggestions given in trail mail.

RE: Can we have multiple tablespaces with in a database.

You mean we can have only single default tablespace for a database but the database objects can be created on different-2 tablespaces? From: amul sul Sent: Friday, February 21, 2020 11:48 AM To: Daulat Ram Cc: pgsql-general@lists.postgresql.org; pgsql-performa...@lists.postgresql.org Subject:

Re: Can we have multiple tablespaces with in a database.

On Fri, Feb 21, 2020 at 11:31 AM Daulat Ram wrote: > Hi Amul , > > Please share the examples how we can create no. of tablespaces for a > single database and how we can use them. > > As I know we can create database on tablespace > >1. CREATE TABLESPACE conn_tbs OWNER enterprisedb LOCATION >

Re: Can we have multiple tablespaces with in a database.

On Fri, Feb 21, 2020 at 11:53 AM Daulat Ram wrote: > That will be great if you share any doc where it’s mentioned that we > can’t use multiple tablespace for a single database. I have to assist my > Dev team regarding tablespaces. > > > > Also , what are the differences between Oracle and

Re: Can we have multiple tablespaces with in a database.

> On Feb 20, 2020, at 22:23, Daulat Ram wrote: > > That will be great if you share any doc where it’s mentioned that we can’t > use multiple tablespace for a single database. I have to assist my Dev team > regarding tablespaces. A single PostgreSQL database can have any number of

RE: Can we have multiple tablespaces with in a database.

What are the differences between Oracle and Postgres tablespace. Can we assign tablespace during Postgres schema creation . As I know in Oracle we assign the tablespace during user/schema creation. -Original Message- From: Christophe Pettus Sent: Friday, February 21, 2020 12:07 PM

Trying to restore a PostgreSQL-9.6 database from an old complete dump and/or a up-to-date just base directory and other rescued files

Hello. I'm trying to restore/rescue a database from some that I have: * I have all the recent files in PGDATA/base (/var/lib/postgresql/9.6/main/base/), but I have not the complete /var/lib/postgresql/9.6/main/ * I have all files from an old backup (and not much different) dump that I