Re: Question about index on different tablespace and rebuild it

Did you check the documentation for alter index? https://www.postgresql.org/docs/10/sql-alterindex.html You could create a script file (plenty of examples on the internet on generating these) and then run through psql or whatever. Also, if you just have a few indexes to move, you could use (for e

Re: Recovery data base!!

On 7 November 2018 06:13:20 CET, Elson Vaz wrote: >Hello people, > > >I need a lot of help, > >Accidentally I deleted all the files from the database, > ... >I have also back up the complete database directory for a month. Install the latest backup. Consider a better backup procedure for the fut

Re: Recovery data base!!

Hi Elson, Do you have the latest basebackup/filesystem backup of the instance? If yes you can recover to the backups taken time. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Recovery data base!!

Hello people, I need a lot of help, Accidentally I deleted all the files from the database, the last copy that I have a day late. I tried with R studio to recover the files, but some files are not correct / corrupted. I have also back up the complete database directory for a month. The postgres

Re: Running pg_upgrade Version 11

On 11/06/2018 06:30 PM, rob stone wrote: On Tue, 2018-11-06 at 15:17 +0900, Michael Paquier wrote: On Tue, Nov 06, 2018 at 04:27:35PM +1100, rob stone wrote: Logged in as user postgres and postgres owns the files created by initdb, so is this a permissions problem or am I having a brain fade?

Re: Question about index on different tablespace and rebuild it

Le mar. 6 nov. 2018 20:49, Condor a écrit : > On 05-11-2018 10:56, Condor wrote: > > Hello, > > > > I have a database that use index on different table space (nvme). I > > read documentation about table space and understand table space cannot > > be treated as an autonomous collection of data fil

Re: Running pg_upgrade Version 11

On Tue, 2018-11-06 at 15:17 +0900, Michael Paquier wrote: > On Tue, Nov 06, 2018 at 04:27:35PM +1100, rob stone wrote: > > Logged in as user postgres and postgres owns the files created by > > initdb, so is this a permissions problem or am I having a brain > > fade? > > Having 0600 as umask for

Re: recursion in plpgsql

David Gauthier writes: > I'm trying/failing to write a recursive plpgsql function where the function > tries to operate on a hierary of records in a reflexive table. > parent-child-grandchild type of recursion. > I tried with a cursor, but got a "cursor already in use" error. So that > looks like

Re: recursion in plpgsql

On Tue, Nov 6, 2018 at 2:54 PM David Gauthier wrote: > Hi: > > I'm trying/failing to write a recursive plpgsql function where the > function tries to operate on a hierary of records in a reflexive table. > parent-child-grandchild type of recursion. > > I tried with a cursor, but got a "cursor alr

recursion in plpgsql

Hi: I'm trying/failing to write a recursive plpgsql function where the function tries to operate on a hierary of records in a reflexive table. parent-child-grandchild type of recursion. I tried with a cursor, but got a "cursor already in use" error. So that looks like scoping. I know I did this

Re: backend crash on DELETE, reproducible locally

Hi, On 2018-11-06 17:11:40 -0500, Tom Lane wrote: > Andres Freund writes: > > On 2018-11-06 16:47:20 -0500, Tom Lane wrote: > >> Looks like somebody forgot to list > >> RELOPT_KIND_GIST in RELOPT_KIND_INDEX, but is that the > >> fault of commit c203d6cf8 or was it busted before? > > > Looks new:

Re: backend crash on DELETE, reproducible locally

Hi, On 2018-11-06 23:11:29 +0100, Tomas Vondra wrote: > On 11/6/18 10:54 PM, Andres Freund wrote: > > Looks new: > > + RELOPT_KIND_INDEX = > > RELOPT_KIND_BTREE|RELOPT_KIND_HASH|RELOPT_KIND_GIN|RELOPT_KIND_SPGIST, > > > > there aren't any other "for all indexes" type options, so the whole > >

Re: backend crash on DELETE, reproducible locally

Andres Freund writes: > On 2018-11-06 16:47:20 -0500, Tom Lane wrote: >> Looks like somebody forgot to list >> RELOPT_KIND_GIST in RELOPT_KIND_INDEX, but is that the >> fault of commit c203d6cf8 or was it busted before? > Looks new: > + RELOPT_KIND_INDEX = > RELOPT_KIND_BTREE|RELOPT_KIND_HASH|

Re: backend crash on DELETE, reproducible locally

On 11/6/18 10:54 PM, Andres Freund wrote: > On 2018-11-06 16:47:20 -0500, Tom Lane wrote: >> =?UTF-8?Q?Ond=c5=99ej_Bouda?= writes: Ondřej, as a short-term workaround you could prevent the crash by setting that index's recheck_on_update property to false. >> >>> Thanks for the tip. I am u

Re: backend crash on DELETE, reproducible locally

On 2018-11-06 16:47:20 -0500, Tom Lane wrote: > =?UTF-8?Q?Ond=c5=99ej_Bouda?= writes: > >> Ondřej, as a short-term workaround you could prevent the crash > >> by setting that index's recheck_on_update property to false. > > > Thanks for the tip. I am unsuccessful using it, though: > > # ALTER IND

Re: backend crash on DELETE, reproducible locally

On 2018-Nov-06, Tom Lane wrote: > =?UTF-8?Q?Ond=c5=99ej_Bouda?= writes: > >> Ondřej, as a short-term workaround you could prevent the crash > >> by setting that index's recheck_on_update property to false. > > > Thanks for the tip. I am unsuccessful using it, though: > > # ALTER INDEX public.sch

Re: backend crash on DELETE, reproducible locally

=?UTF-8?Q?Ond=c5=99ej_Bouda?= writes: >> Ondřej, as a short-term workaround you could prevent the crash >> by setting that index's recheck_on_update property to false. > Thanks for the tip. I am unsuccessful using it, though: > # ALTER INDEX public.schedulecard_overlap_idx SET (recheck_on_update

Re: backend crash on DELETE, reproducible locally

Ondřej, as a short-term workaround you could prevent the crash by setting that index's recheck_on_update property to false. Thanks for the tip. I am unsuccessful using it, though: # ALTER INDEX public.schedulecard_overlap_idx SET (recheck_on_update = FALSE); ERROR: unrecognized parameter "r

Re: why select count(*) consumes wal logs

Le mar. 6 nov. 2018 12:40 PM, Tom Lane a écrit : > Ravi Krishna writes: > > I loaded 133 million rows to a wide table (more than 100 cols) via COPY. > The table has > > no index at this time. Since I am the only user I don't see any other > activity. > > Now when I run select count(*) on the tab

Re: backend crash on DELETE, reproducible locally

I wrote: > Interestingly, it doesn't crash if I change the index type to btree, > which I was not expecting because the crashing code seems pretty > independent of the index type. Oh ... duh. The problem here is that ProjIndexIsUnchanged thinks that the type of the index column is identical to th

Re: Idle query that's not ""?

On 11/06/2018 03:04 PM, David G. Johnston wrote: On Tue, Nov 6, 2018 at 1:59 PM Ron wrote: Right. But when does the query text become ""? Or has that become obsolete? (We recently migrated from 8.4.) That behavior changed sometime around 9.0; since it always shows the last query executed it

Re: Idle query that's not ""?

On Tue, Nov 6, 2018 at 1:59 PM Ron wrote: > > Right. But when does the query text become ""? Or has that become > obsolete? (We recently migrated from 8.4.) That behavior changed sometime around 9.0; since it always shows the last query executed it logically follows that it will never show the

Re: backend crash on DELETE, reproducible locally

=?UTF-8?Q?Ond=c5=99ej_Bouda?= writes: >>> Hm, what are the data types of those columns? > scheduletemplate_id bigint NOT NULL, > period_day smallint NOT NULL, > timerange timerange NOT NULL, OK, so here's a minimal reproducer: drop table schedulecard; create table schedulecard ( scheduletemp

Re: Idle query that's not ""?

Right.  But when does the query text become ""?  Or has that become obsolete? (We recently migrated from 8.4.) On 11/06/2018 02:53 PM, Hellmuth Vargas wrote: Hi In the documentation describes the data in this field: https://www.postgresql.org/docs/10/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

Re: Idle query that's not ""?

On Tue, Nov 6, 2018 at 1:46 PM Ron wrote: > Why do these idle queries (pids 8357, 11260 and 11355) "remember" the queries > they ran instead of having the text ""? Because having the text "" is redundant with the field status=idle David J.

Re: Idle query that's not ""?

Hi In the documentation describes the data in this field: https://www.postgresql.org/docs/10/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW "Text of this backend's most recent query. If state is active this field shows the currently executing query. In all other states, it shows the last query that

Idle query that's not ""?

Hi, v9.6.6 Why do these idle queries (pids 8357, 11260 and 11355) "remember" the queries they ran instead of having the text ""? postgres=# select pid,    xact_start as txn_start,    to_char(EXTRACT(epoch FROM now() - query_start), '999,999.') as query_age_secs,

Re: backend crash on DELETE, reproducible locally

> Hm, what are the data types of those columns? scheduletemplate_id bigint NOT NULL, period_day smallint NOT NULL, timerange timerange NOT NULL, where timerange is defined as follows: CREATE TYPE public.timerange AS RANGE ( SUBTYPE=time, SUBTYPE_OPCLASS = time_ops ); > And I assume you

Re: Fwd: Log file

On 11/06/2018 12:06 PM, Igor Korot wrote: [snip] Ok. I guess I will have to write such function. Cron and the relevant log_* config variables should solve your problems. -- Angular momentum makes the world go 'round.

Re: backend crash on DELETE, reproducible locally

> Foreign-key triggers too? There are four trivial foreign keys from public.schedulecard like this: ALTER TABLE public.schedulecard ADD CONSTRAINT fk_schedulecard_schedulecard FOREIGN KEY (schedulecard_id) REFERENCES public.schedulecard (id) MATCH SIMPLE ON UPDATE NO ACTION ON

Re: backend crash on DELETE, reproducible locally

=?UTF-8?Q?Ond=c5=99ej_Bouda?= writes: > There are some simple btree indexes due to foreign keys, and this one, > which seems as the cause for the crashes: > CREATE INDEX schedulecard_overlap_idx > ON public.schedulecard USING gist > (scheduletemplate_id, (period_day::integer % 7), time

Re: backend crash on DELETE, reproducible locally

Alvaro Herrera writes: > What indexes are there in this table? Indexes on expressions are > particularly suspect. I had not looked at the "projection index" code before, and now that I have, I am desperately unhappy with it. It's seriously buggy, badly underdocumented, unbelievably inefficient,

Re: backend crash on DELETE, reproducible locally

=?UTF-8?Q?Ond=c5=99ej_Bouda?= writes: > I thought triggers should be suspected. However, even when all the three > triggers have been disabled (ALTER TABLE DISABLE TRIGGER), the UPDATE > crashed the server. Foreign-key triggers too? > What else could I try? A stack trace would be really helpf

Re: backend crash on DELETE, reproducible locally

Dne 6.11.2018 v 20:45 Alvaro Herrera napsal(a): What indexes are there in this table? Indexes on expressions are particularly suspect. There are some simple btree indexes due to foreign keys, and this one, which seems as the cause for the crashes: CREATE INDEX schedulecard_overlap_idx O

Re: Question about index on different tablespace and rebuild it

On 05-11-2018 10:56, Condor wrote: Hello, I have a database that use index on different table space (nvme). I read documentation about table space and understand table space cannot be treated as an autonomous collection of data files. My question is: Is this always true ? I mean if I have table

Re: backend crash on DELETE, reproducible locally

On 2018-Nov-06, Ondřej Bouda wrote: > > Hmm, this one smells like c203d6cf81b4 -- haven't seen any fixes for > > that one. Can you share more details on this? I think the failing > > update is on table with oid=557732818, but I might be wrong. > > That's exactly the table, public.schedulecard.

Re: backend crash on DELETE, reproducible locally

> Hmm, this one smells like c203d6cf81b4 -- haven't seen any fixes for > that one. Can you share more details on this? I think the failing > update is on table with oid=557732818, but I might be wrong. That's exactly the table, public.schedulecard. We issue an UPDATE changing some of its column

Re: Trouble Upgrading Postgres

The column is a bytea. I'm working out a way to limit the size on the front end. Chuck On Tue, Nov 6, 2018 at 1:44 PM wrote: > As someone pointed out, there is a limit with bytea (Blob's). > To test if it is bytea, use a COPY with a select statement : > > COPY ( select A, B,C ,D ...etc FROM t

Re: backend crash on DELETE, reproducible locally

On 2018-Nov-06, Ondřej Bouda wrote: > So we dumped and restored all our databases. After that, the crash on DELETE > never occurred (before, it was several times a day). However, the crash on > UPDATE still occurs on specific rows. We are quite certain no ALTER TABLE > statement was executed on th

RE: Trouble Upgrading Postgres

As someone pointed out, there is a limit with bytea (Blob's).To test if it is bytea, use a COPY with a select statement :COPY ( select A, B,C ,D ...etc FROM table ) TO 'outfile' ;Leaveing out the bytea column.If this works, then then one of the bytea columns is way to big.Ben Duncan - Business Netw

Re: why select count(*) consumes wal logs

Michael Nolan writes: > On Tue, Nov 6, 2018 at 11:40 AM Tom Lane wrote: >> That represents setting the yes-this-row-is-committed hint bits on the >> newly loaded rows. The first access to any such row will set that bit, >> whether it's a select or a VACUUM or whatever. > Tom, does that include

Re: why select count(*) consumes wal logs

On Tue, Nov 6, 2018 at 11:40 AM Tom Lane wrote: > > That represents setting the yes-this-row-is-committed hint bits on the > newly loaded rows. The first access to any such row will set that bit, > whether it's a select or a VACUUM or whatever. > > Tom, does that include ANALYZE? -- Mike Nolan

Re: Fwd: Log file

Hi, guys, For some reason this reply was in my "Spam" folder (gmail service). I guess moving to the new mailer service is not completely safe for "Spam". So replying now - apologies to be late. On Wed, Oct 31, 2018 at 2:00 AM Laurenz Albe wrote: > > Igor Korot wrote: > > Now is there a command t

Re: why select count(*) consumes wal logs

>That represents setting the yes-this-row-is-committed hint bits on the >newly loaded rows.  The first access to any such row will set that bit, >whether it's a select or a VACUUM or whatever. yes now I recollect reading this in a blog. Thanks Tom.

Re: Trouble Upgrading Postgres

Adrian Klaver writes: > On 11/6/18 8:27 AM, Daniel Verite wrote: >> Adrian Klaver wrote: >>> To me that looks like a bug, putting data into a record you cannot get out. >> Strictly speaking, it could probably get out with COPY in binary format, >> but pg_dump doesn't use that. Another possibilit

Re: why select count(*) consumes wal logs

Ravi Krishna writes: > I loaded 133 million rows to a wide table (more than 100 cols) via COPY. The > table has > no index at this time. Since I am the only user I don't see any other > activity. > Now when I run select count(*) on the table where I just loaded data, it runs > for ever, > more

RE: why select count(*) consumes wal logs

I concord. Why VACUUM when there is no update or deletes. Regards, Virendra From: Ron [mailto:ronljohnso...@gmail.com] Sent: Tuesday, November 06, 2018 12:20 PM To: pgsql-general@lists.postgresql.org Subject: Re: why select count(*) consumes wal logs On 11/06/2018 11:12 AM, Michael Nolan wrote:

Re: why select count(*) consumes wal logs

On 11/06/2018 11:12 AM, Michael Nolan wrote: On Tue, Nov 6, 2018 at 11:08 AM Ravi Krishna > wrote: PG 10.5 I loaded 133 million rows to a wide table (more than 100 cols) via COPY. It's always a good idea after doing a large scale data load to do a vacuum an

Re: why select count(*) consumes wal logs

Must be something to do with Vaccum as the second time I ran the SQL, it did not consume WAL logs.

Re: why select count(*) consumes wal logs

On Tue, Nov 6, 2018 at 11:08 AM Ravi Krishna wrote: > PG 10.5 > > I loaded 133 million rows to a wide table (more than 100 cols) via COPY. > It's always a good idea after doing a large scale data load to do a vacuum analyze on the table (or the entire database.) -- Mike Nolan

why select count(*) consumes wal logs

PG 10.5 I loaded 133 million rows to a wide table (more than 100 cols) via COPY. The table has no index at this time. Since I am the only user I don't see any other activity. Now when I run select count(*) on the table where I just loaded data, it runs for ever, more than 10min and still runnin

Re: Trouble Upgrading Postgres

On 11/6/18 8:27 AM, Daniel Verite wrote: Adrian Klaver wrote: So there's no way it can deal with the contents over 500MB, and the ones just under that limit may also be problematic. To me that looks like a bug, putting data into a record you cannot get out. Strictly speaking, it cou

Re: PgAgent on Windows

Thank´s Stéphane, My pg_pass.conf in database_name parameter was postgres, but when I change for *, then run successful. Thank´s again, Márcio. [image: Mailtrack] Remetente notificado por Mailtrack

Re: Trouble Upgrading Postgres

Adrian Klaver wrote: > > So there's no way it can deal with the contents over 500MB, and the > > ones just under that limit may also be problematic. > > To me that looks like a bug, putting data into a record you cannot get out. Strictly speaking, it could probably get out with COPY in b

Re: PgAgent on Windows

Le 06/11/2018 à 14:36, Marcio Meneguzzi a écrit : Hello, I´m trying use pgAgent on windows 10 and Windows Server 2012 R2. My version of PostgreSQL is 9.5.1.14 Install and configure pgAgent with sucess, but, when I try run a Job, status is Failed with a message bellow: *"Couldn't create the prim

Re: PgAgent on Windows

Run a Job is: In PgAdminit´s right click over the name of job and select option "Run Now". After, when i open windows log aplication´s, a message like this "*"Couldn't create the primary connection (attempt 1): fe_sendauth: no password supplied" is generated.* *Thank´s.* [image: Mailtrack]

Re: Trouble Upgrading Postgres

On 11/6/18 3:47 AM, Daniel Verite wrote: Charles Martin wrote: but the second one returned this: 0 "623140" 1 "53" 2 "12" 3 "10" 4 "1" 5 "1" 7 "1" [null] "162" Not quite sure what that means, but if there is just a small number of overly-large records, I might be able to delete them

Re: Trouble Upgrading Postgres

Thanks, Daniel. Using your idea, I found the records over 400MB, and deleted them in the application. The largest two were in inactive matters, and the third is still available elsewhere if needed. I'll try pg_dump again after work hours and see if it works now. Hopefully it will, now that I've A

Re: Running pg_upgrade Version 11

On 11/5/18 9:27 PM, rob stone wrote: Hello, Trying to run pg_upgrade from version 10 to version 11 on the test server and pulling the following error:- could not open version file: /home/postgres/testing/data_v10/PG_VERSION Failure, exiting O/S is:- Debian 4.18.10-2 (2018-11-02) x86_64 GNU/Lin

Re: PgAgent on Windows

On 11/06/2018 07:36 AM, Marcio Meneguzzi wrote: Hello, I´m trying use pgAgent on windows 10 and Windows Server 2012 R2. My version of PostgreSQL is 9.5.1.14 Install and configure pgAgent with sucess, but, when I try run a Job, status is Failed with a message bellow: *"Couldn't create the primar

PgAgent on Windows

Hello, I´m trying use pgAgent on windows 10 and Windows Server 2012 R2. My version of PostgreSQL is 9.5.1.14 Install and configure pgAgent with sucess, but, when I try run a Job, status is Failed with a message bellow: *"Couldn't create the primary connection (attempt 1): fe_sendauth: no password

Re: CREATE OR REPLACE FUNCTION statement just sitting there

On 2018-Nov-06, Ron wrote: > On 11/06/2018 05:34 AM, Alvaro Herrera wrote: > I did see it, but the https://wiki.postgresql.org/wiki/Lock_Monitoring query > seems to work (seeing that it regularly shows locks). > > Is this query from https://stackoverflow.com/a/43363536/1543618 adequate to > the

Re: CREATE OR REPLACE FUNCTION statement just sitting there

On 11/06/2018 05:34 AM, Alvaro Herrera wrote: On 2018-Nov-05, Ron wrote: That (plus pg_locks)  is the heart of the "list all blocking queries" statement I copied from https://wiki.postgresql.org/wiki/Lock_Monitoring. On that page there's a note about 9.6. Did you see the referenced commit htt

Re: CREATE OR REPLACE FUNCTION statement just sitting there

On 11/06/2018 05:05 AM, Laurenz Albe wrote: Ron wrote: However, one or more of our big (and schema-identical) prod databases (which are each on a different server) it is finicky and tends to just "sit" at a random one of the CREATE OR REPLACE FUNCTION statements. The "list all blocking querie

Re: Trouble Upgrading Postgres

Charles Martin wrote: > but the second one returned this: > > 0 "623140" > 1 "53" > 2 "12" > 3 "10" > 4 "1" > 5 "1" > 7 "1" > [null] "162" > > Not quite sure what that means, but if there is just a small number of > overly-large records, I might be able to delete them. If I can find the

Re: CREATE OR REPLACE FUNCTION statement just sitting there

On 2018-Nov-05, Ron wrote: > That (plus pg_locks)  is the heart of the "list all blocking queries" > statement I copied from https://wiki.postgresql.org/wiki/Lock_Monitoring. On that page there's a note about 9.6. Did you see the referenced commit https://git.postgresql.org/gitweb/?p=postgresql.

Re: CREATE OR REPLACE FUNCTION statement just sitting there

Ron wrote: > > > However, one or more of our big (and schema-identical) prod databases > > > (which are each on a different server) > > > it is finicky and tends to just "sit" at a random one of the CREATE OR > > > REPLACE FUNCTION statements. > > > > > > The "list all blocking queries" I run do