Re: Unexpected block ID found when reading data
Hi, Adrian Thanks in advance. pdgump command: pg_dump -U myuser -Fc -Z3 -d mydb > dbdump.backup I'm not sure which pg_dump version did i use before, but I used psql 12.5 to dump and the db version is postgresql 9.6. pgrestore command: pg_restore -U myuser -j8 -d mydb dbdump.backup I’ve tried to restore to postgre 9.6, 12.1 and 12.5 > On 3 Aug 2021, at 09.11, Adrian Klaver wrote: > > On 8/2/21 6:42 PM, Gilar Ginanjar wrote: >> Hi, >> I have a db dump (.backup) that i want to restore. But it comes up with >> error: >> "found unexpected block ID when reading data" >> Is there any chance i can save my backup? The problem is I don't have the >> database anymore. >> I used psql 12.5 when dumping the database approx a year ago. > > What command and version of pg_dump did you use to dump it? > > What command and program(and version) did you use to restore it? > > What version of Postgres where you restoring to? > > > -- > Adrian Klaver > adrian.kla...@aklaver.com
Re: Unexpected block ID found when reading data
Hi, Adrian Thanks in advance. pdgump command: pg_dump -U myuser -Fc -Z3 -d mydb > dbdump.backup I'm not sure which pg_dump version did i use before, but I used psql 12.5 to dump and the db version is postgresql 9.6. pgrestore command: pg_restore -U myuser -j8 -d mydb dbdump.backup I’ve tried to restore to postgre 9.6, 12.1 and 12.5 > On 3 Aug 2021, at 09.11, Adrian Klaver wrote: > > On 8/2/21 6:42 PM, Gilar Ginanjar wrote: >> Hi, >> I have a db dump (.backup) that i want to restore. But it comes up with >> error: >> "found unexpected block ID when reading data" >> Is there any chance i can save my backup? The problem is I don't have the >> database anymore. >> I used psql 12.5 when dumping the database approx a year ago. > > What command and version of pg_dump did you use to dump it? > > What command and program(and version) did you use to restore it? > > What version of Postgres where you restoring to? > > > -- > Adrian Klaver > adrian.kla...@aklaver.com
Re: Unexpected block ID found when reading data
On 8/2/21 6:42 PM, Gilar Ginanjar wrote: Hi, I have a db dump (.backup) that i want to restore. But it comes up with error: "found unexpected block ID when reading data" Is there any chance i can save my backup? The problem is I don't have the database anymore. I used psql 12.5 when dumping the database approx a year ago. What command and version of pg_dump did you use to dump it? What command and program(and version) did you use to restore it? What version of Postgres where you restoring to? -- Adrian Klaver adrian.kla...@aklaver.com
Unexpected block ID found when reading data
Hi, I have a db dump (.backup) that i want to restore. But it comes up with error: "found unexpected block ID when reading data" Is there any chance i can save my backup? The problem is I don't have the database anymore. I used psql 12.5 when dumping the database approx a year ago.
Re: Lazy View's Column Computing
Mladen Gogala writes: > The query from the view would probably merge view with the original and > optimize everything as a single query. Unfortunately, there is no way to > tell: > mgogala=# explain select ename,job,sal from acct_view; > QUERY PLAN > > Seq Scan on emp (cost=0.00..1.18 rows=3 width=21) > Filter: (deptno = 10) > (2 rows) > The only tool that you have at your disposal is EXPLAIN. No ... EXPLAIN VERBOSE would fill in the detail you want, as somebody else already demonstrated in this thread. regards, tom lane
Re: Lazy View's Column Computing
For all that we know, it may already be happening. That looks like a pretty reasonable optimization which may already be in place. If we create a view: mgogala=# select * from dept; deptno | dname | loc ++-- 10 | ACCOUNTING | NEW YORK 20 | RESEARCH | DALLAS 30 | SALES | CHICAGO 40 | OPERATIONS | BOSTON (4 rows) mgogala=# create view acct_view as select * from emp where deptno=10; CREATE VIEW The query from the view would probably merge view with the original and optimize everything as a single query. Unfortunately, there is no way to tell: mgogala=# explain select ename,job,sal from acct_view; QUERY PLAN Seq Scan on emp (cost=0.00..1.18 rows=3 width=21) Filter: (deptno = 10) (2 rows) The only tool that you have at your disposal is EXPLAIN. What we need to ascertain that assumption is an optimizer trace file detailing the decisions made by optimizer, something like the event 10053 from another database which will remain unnamed. Merging the view query into the top level query would produce something like this: mgogala=# select ename,job,sal from emp mgogala-# where deptno=10; ename | job | sal +---+-- CLARK | MANAGER | 2450 KING | PRESIDENT | 5000 MILLER | CLERK | 1300 (3 rows) The table, shown below, has more columns than the 3 used in the above query: mgogala=# \d emp Table "mgogala.emp" Column | Type | Collation | Nullable | Default --+-+---+--+- empno | smallint | | not null | ename | character varying(10) | | | job | character varying(9) | | | mgr | smallint | | | hiredate | timestamp without time zone | | | sal | double precision | | | comm | double precision | | | deptno | smallint | | | Indexes: "emp_pkey" PRIMARY KEY, btree (empno) Foreign-key constraints: "fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno) Merging the top level query with the view query would be smart tactic which is probably already deployed. However, it is not possible to tell with the tools at hand. That is what you want: the query touches only the columns you need, nothing else. That is done by the query optimizer in the "rewrite" phase of the query. https://www.postgresql.org/docs/12/query-path.html I could bet that the top level query gets merged with the view query during the rewrite and that the columns that aren't needed aren't touched. That in particular means that the function computing an untouched column of the query isn't executed as it is. Regards Regards On 8/2/21 10:12 AM, Avi Weinberg wrote: Hi, Is there a way to compute a column in a view only if it is referenced in the query? I have a view's column that its value is computed by a function. If in the query that column is not used at all, can Postgres "skip" computing it? Thanks! IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: pgcrypto - real life examples to encrypt / decrypt
On 8/2/21 2:14 PM, Vikas Sharma wrote: Dear Experts, Could you please share some real life examples of using pgcrypto in production? I am planning to use it in our environment and wondering what could be the best practice for its use. I would start by doing a search on 'using pgcrypto', that will return articles/blogs with pointers. Thank you. Regards Vikas S -- Adrian Klaver adrian.kla...@aklaver.com
Re: single-row deadlock
After upgrading from PostgreSQL 9.6 to 13 we started to experience locking issues once per week or two. This turned out to be a performance problem and reused pid-s. No deadlock.
pgcrypto - real life examples to encrypt / decrypt
Dear Experts, Could you please share some real life examples of using pgcrypto in production? I am planning to use it in our environment and wondering what could be the best practice for its use. Thank you. Regards Vikas S
Re: Postgres 9.6 to 12.7 pg_upgrade error - terminating connection due to administrator command
On Mon, Aug 2, 2021 at 12:20 PM Dhanush D wrote: > It doesn't show up twice based on these checks (Is there any other way I > can check if the seq is showing up twice?). > > SELECT * FROM pg_class WHERE relkind = 'S' AND relname ~ 'permission'; I made the "relname" a bit broad, you should see the exact name (add an order by...). relkind=S are sequences. David J.
Re: Postgres 9.6 to 12.7 pg_upgrade error - terminating connection due to administrator command
On Mon, Aug 2, 2021 at 11:42 AM Dhanush D wrote: > Multiple "Create sequence" and "Drop sequence". However the drop sequence > doesn't seem to drop the sequence cleanly hence failing the restore process. > It shouldn't be dropping at all - it assumes it is starting from an empty slate. Same question as before, on the 9.6 instance, does this sequence actually show up twice? If so, how it came to be that way might be interesting, and others may wish to investigate, but ultimately one of the two would have to be removed (not something I feel qualified to walk someone else through via email) from the 9.6, after which the upgrade should get past that point. David J.
Re: ERROR: ImportError: No module named 'psutil'
Ok, thanks guys. Let me check that. On Sun, Aug 1, 2021 at 10:05 PM Tom Lane wrote: > Adrian Klaver writes: > > In that vein the OP could run: > > > DO $$ > > import sys > > plpy.notice(sys.version) > > $$ LANGUAGE plpython3u; > > > in the database to see what plpython3u is actually pointing at? > > +1 ... looking at sys.path in the same way would also be useful. > > regards, tom lane >
Re: Lazy View's Column Computing
On Mon, 2 Aug 2021 at 19:53, Tom Lane wrote: > Avi Weinberg writes: > > Is there a way to compute a column in a view only if it is referenced in > the query? I have a view's column that its value is computed by a > function. If in the query that column is not used at all, can Postgres > "skip" computing it? > > If the function is not volatile, and you're using a moderately recent PG > version, I'd expect the planner to do that for you. something like this ? postgres=# table t; -[ RECORD 1 ] col1 | 100 col2 | 100 col3 | 100 -- the sleep is intentional to postgres=# create or replace function demo(int) returns int as $$ begin perform pg_sleep(10); return $1::int; end; $$ language plpgsql immutable; CREATE FUNCTION Time: 7.253 ms -- we create a view where col2 is a result of an immutable function call from demo postgres=# create or replace view myview as select col1, demo(col2)::int as col2 from t; CREATE VIEW Time: 7.952 ms postgres=# \x Expanded display is off. postgres=# explain (analyze,verbose) select col1, col2 from myview; QUERY PLAN --- Seq Scan on public.t (cost=0.00..540.40 rows=2040 width=8) (actual time=10010.231..10010.236 rows=1 loops=1) Output: t.col1, demo(t.col2) Query Identifier: 291510593965093899 Planning Time: 0.027 ms Execution Time: 10010.250 ms -- the function demo was called which resulted in slow exec time (5 rows) Time: 10010.648 ms (00:10.011) postgres=# explain (analyze,verbose) select col1 from myview; QUERY PLAN -- Seq Scan on public.t (cost=0.00..30.40 rows=2040 width=4) (actual time=0.005..0.006 rows=1 loops=1) Output: t.col1 Query Identifier: 8513308368843926789 Planning Time: 0.030 ms Execution Time: 0.015 ms -- no function call as col2 not part of select from view (5 rows) Time: 0.222 ms > -- > Thanks, Vijay Mumbai, India
Re: Lazy View's Column Computing
Avi Weinberg writes: > Is there a way to compute a column in a view only if it is referenced in the > query? I have a view's column that its value is computed by a function. If > in the query that column is not used at all, can Postgres "skip" computing it? If the function is not volatile, and you're using a moderately recent PG version, I'd expect the planner to do that for you. regards, tom lane
Lazy View's Column Computing
Hi, Is there a way to compute a column in a view only if it is referenced in the query? I have a view's column that its value is computed by a function. If in the query that column is not used at all, can Postgres "skip" computing it? Thanks! IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
Re: Automatic backup in windows
On Sunday, August 1, 2021, obi reddy wrote: > Hello everyone, I want to schedule the automatic backup in windows > postgresql platform. > > I have attached the script, if any changes is required please correct and > send back. This is my humble request. > Actually I don't know to write the windows script. Otherwise if incase > any have the script please send me . > Then maybe you should use one of the various backup tools out there instead of setting up your own. Don’t forget to test restoration. David J.
Automatic backup in windows
Hello everyone, I want to schedule the automatic backup in windows postgresql platform. I have attached the script, if any changes is required please correct and send back. This is my humble request. Actually I don't know to write the windows script. Otherwise if incase any have the script please send me . Db name:rathi26 Backup file path:C:\Users\gangireddyobiredy\Desktop\case.sql Thank you And Regards Obireddy. G @echo off SET day=%date:~0,2% SET month=%date:~7,8% SET year=%date:~6,4% SET hh=%time:~0,2% SET mm=%time:~3,2% SET BACKUPDIR=C:\users_Backup SET datestr=%day%-%month%-%year%_%hh%-%mm% SET dir=%day%-%month%-%year% mkdir C:\users_Backup%dir% #Provide database name here SET db1=dbname1 echo datestr is %datestr% SET BACKUP_rathi26=C:\Users\gangireddyobiredy\Desktop\case.sqll SET rathi26=%db1%%datestr%.sql ECHO Backup file name is %rathi26% SET PGPASSWORD= echo on #Execute Backup for database C:\Program Files\PostgreSQL\13\bin>pg_dump.exe -p 5432 -U postgres -v -d rathi26 > C:\Users\gangireddyobiredy\Desktop\case.sql echo Backup Completed
