used
only to log on to postgres without password.
[1]:
https://docs.devart.com/studio-for-postgresql/connecting-to-db/generating-ssl-certificate.html
Andrus
is there some exisising wal decoder in C# or as command-line utility?
Posted also in
https://stackoverflow.com/questions/79518438/how-to-convert-wal-log-to-sql-commands-from-given-time
Andrus
ebian 16.4-1.pgdg120+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 12.2.0-14) 12.2.0
Posted also in
https://stackoverflow.com/questions/78938204/how-to-grant-role-to-user
Andrus.
cting Refresh does
nothing.
Smells like a bug.
Right clicking in Tables and selecting Refresh worked.
Andrus.
min2;
pgAdmin shows revoke commands for those users:
REVOKE ALL ON TABLE public.kaspriv FROM admin1;
REVOKE ALL ON TABLE public.kaspriv FROM admin2;
How to prevent pgAdmin to show those revokes?
Andrus.
users like
GRANT SELECT ON TABLE public.kaspriv TO paide;
How to remove user-spefic grants ?
Andrus.
Hi!
I tried
alter role alekspoluh reset all
After this command pgAdmin still shows revoke and grant commands for
alekspoluh role.
How to remove all grant and revoke assignments for role ?
Andrus.
03.06.2023 20:50 Andrus kirjutas:
Hi!
> REVOKE must be executed by the gran
Hi!
Or just execute those REVOKE ALL commands (except for PUBLIC) that pgAdmin
already gives you.
pgAdmin gives
REVOKE ALL ON TABLE public.kaspriv FROM someuser;
I ran it but pgAdmin still gives this statement.
Andrus.
/questions/76394896/how-to-remove-unnecessary-grant-and-revoke-privileges-from-table
Andrus.
Hi!
Using index
create index on toode ( *split_part( toode, '/',1) *)
and query
select toode.toode , n2, n3, n4
from toode, vordlusajuhinnak
where *split_part( toode.toode, '/',1) *= vordlusajuhinnak.toode;
reduces run time to 5 minutes.
Andrus.
23.05.2023 17:26 A
uhinnak.toode;
used and keeping existing table structure? Functional index should
produce same speed improvement as using separate column?
Andrus.
gt; Parallel Index Only Scan using toode_pkey on toode
(cost=0.55..95029.93 rows=303869 width=60)"
" -> Seq Scan on vordlusajuhinnak (cost=0.00..721.33 rows=39433
width=32)"
How to speed it up?
Andrus.
23.05.2023 14:32 Bzm@g kirjutas:
Great,
However I think it
possible, if this helps.
Tried also using like:
WHERE toode.toode=vordlusajuhinnak.toode OR
toode.toode LIKE vordlusajuhinnak.toode||'/%'
Posted also in
https://stackoverflow.com/questions/76311957/how-to-match-character-columns-with-separated-subcolumns
Andrus.
ery used as an expression
How to pass set of integers to function? Should temp table with fixed
name used or is there better solution?
Using Postgresql 12+
Andrus.
restore using custom format are much slower.
Andrus.
--
Erik
e are no file size limits. Error
message is not about this.
Backup client is running in Windows 11 and this cannot changed.
How to create backup in format from which tables can selectively restored?
Posted also in
https://stackoverflow.com/questions/75387616/how-to-make-directory-format-backup
Andrus.
SL
Client is pg_dump from Postgres 15 running in Windows 11
Andrus
yback can continue?
Master server is in Postgres 12 in Debian 10.3
Hot standby in Postgres 12 in WSL Debian 11.5
Can Debian version 10.3/11.5 difference cause this exception?
Andrus.
postgresql-12
How to install Postgres 12 in Debian 11.5 ?
Will this replication work since Debian versions are different but
hopefully locale implementations are same ?
Which is most reasonable way to replicate whole Postgres 12/Debian 10.3
cluster in Windows 11 ?
Andrus.
gres. ColWidth is immutable and called with
constant arguments so it should work. How to fix postgres to allow
constant ColWidth() expression in cast ?
Andrus.
should it replaced with dynamic query like
execute 'select ' || p_field || ' from ' || p_table || ' limit 0'
and get column size from this query result somehow ?
Andrus.
c', 'test', 'charcol') )
FROM Test
as Adrian Klaver recommends in
https://stackoverflow.com/questions/74061290/how-to-return-argument-datatype-from-sql-function#comment130780708_74061290
at this worked. In this best solution?
How to remove p_namespace parameter from colwidth()? ColWidth() should
return column width in first search_path table just like select ... from
test finds table test.
Andrus.
);
$f$ LANGUAGE SQL ;
torusbpchar(charcol) still returns text data type.
npgsql DataReader is used to get data.
Andrus.
27;%,'||trim(artliik.liiginrlki)||',%'
returns date for single integer list only.
How to change join so that type ranges in list like 6-9 are also returned?
Eq. f list contains 6-9, Type 6,7,8 and 9 shoud included in report.
Postgres 13 is used.
Posted also in
https://stackoverflow.com/questions/72404218/how-determine-if-range-list-contains-specified-integer
Andrus.
C++ build 1914, 64-bit
and psqlODBC driver.
Andrus.
Hi!
It worked.
Thank you very much.
Andrus.
13.02.2022 16:46 Torsten Förtsch kirjutas:
WITH x AS (
SELECT *
, sum(hours) OVER w AS s
FROM hours
WINDOW w AS (PARTITION BY person ORDER BY job_id)
)
SELECT *
, greatest(least(s, 120) - coalesce(lag(s, 1) OVER w, 0), 0) AS
column
in hours table for every person.
Andrus.
13.02.2022 14:46 Torsten Förtsch kirjutas:
something like
SELECT *
, least(sum(hours) OVER w, 120) AS regular
, greatest(sum(hours) OVER w - 120, 0) AS overtime
FROM hours
WINDOW w AS (PARTITION BY person ORDER BY job_id);
job_id
of
hours column in hours table for every person.
Note that since hours running total becomes greater than 120 in job 2,
job 2 hours should appear in both hours columns.
Maybe window functions can used.
Andrus.
ompiled by Visual C++ build 1900, 64-bit
Posted also in
https://stackoverflow.com/questions/67683299/on-conflict-do-nothing-clause-is-ignored-on-insert
Andrus.
s file "pg_stat_tmp/global.tmp" to
"pg_stat_tmp/global.stat": Permission denied
2021-03-21 23:51:25 EET autovacuum worker LOG: using stale
statistics instead of current ones because stats collector is not responding
Andrus.
ot rename
temporary statistics file "pg_stat_tmp/global.tmp" to
"pg_stat_tmp/global.stat": Permission denied
Andrus.
Hi!
Have you tested the unpatched builds?
No.
Andrus.
Hi!
Okay, cool. I am going to send you privately two links to the builds
I am going to produce, 13.2 unpatched and 13.2 patched.
I replaced files in 13.1 server with ones from your patched version.
There are no errors in log file now for 8 hours.
Andrus.
quot;
"9","","0x7ffc7639af04","0x7ffc7639af04",""
"10","","0x7ffc7728fe86","0x7ffc7728fe86",""
"11","","0x7ffc7728f622","0x7ffc7728f622",""
"12","","0x7ffc77290a46","0x7ffc77290a46",""
"13","","0x14048ccca","0x14048ccca",""
"14","","0x14009463b","0x14009463b",""
"15","","0x140094365","0x140094365",""
"16","","0x14008e541","0x14008e541",""
"17","","0x140286f46","0x140286f46",""
"18","","0x1400a17ca","0x1400a17ca",""
"19","","0x1402923cb","0x1402923cb",""
"20","","0x1401b95c0","0x1401b95c0",""
"21","","0x14049f304","0x14049f304",""
"22","","0x7ffc77bb7974","0x7ffc77bb7974",""
"23","","0x7ffc7a13a2d1","0x7ffc7a13a2d1",""
It looks like too small buffer is passed to NtQueryInformationFile .
Andrus.
17:07:09,9668956 postgres.exe 11800 QueryDirectory C:\Program
Files\PostgreSQL\13\data\pg_wal NO MORE FILES FileInformationClass:
FileBothDirectoryInformation
Andrus.
red Access: Read Attributes, Delete, Synchronize,
Disposition: Open, Options: Synchronous IO Non-Alert, Open Reparse
Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a
Andrus.
>On Wed, Mar 17, 2021 at 09:25:00AM +0200, Andrus wrote:
pg_config --configure outputs
--enable-thread-safety --enable-nls --with-ldap --with-openssl --with-uuid
--with-libxml --with-libxslt --with-icu --with-tcl --with-perl --with-python
Thanks. Do you actually use OpenSSL,
Not direc
Hi
>I am not completely sure which flags your installation has, but
pg_config --configure outputs
--enable-thread-safety --enable-nls --with-ldap --with-openssl
--with-uuid --with-libxml --with-libxslt --with-icu --with-tcl
--with-perl --with-python
Andrus.
with them.
Andrus.
on those servers.
In one of those servers, do you have in pg_wal/ some files named
xlogtemp.N? N is an integer that would be the PID of the process that
generated it.
No. Intel server has 4 files with .deleted extension. AMD server has no
.deleted files. It has probably lower traffic.
Andrus.
04F0082": Permission denied
So It should be probably reproducible in any Windows 2019 server.
Andrus.
pt/FAQ#Where_are_older_versions_of_the_packages.3F
<https://wiki.postgresql.org/wiki/Apt/FAQ#Where_are_older_versions_of_the_packages.3F>
I discovered that Debian allows to install 9.3 side-by side with 12
apt-get install postgresql-9.3
Andrus.
stall it in new Debian ?
Or is it possible to download ISO image with Postgres 9.3 installed ?
Or should I try to find old Debian ISO image and install it into this?
Andrus.
to it. How to install
postgres 9.3 in new Debian ?
Andrus.
ta from 32 or 64 bit server ?
Should I create virtual machine and install Linux with 9.3 into it or is
there simpler solution?
Will data from 32 bit server require 32 bit Linux with 32 bit postgres
or will it work with 64 bit Linux also ?
Andrus.
indows but error is the same.
How to recover data from 9.3 linux directory ?
Andrus.
00A0072
postgres.exe 20048 File C:\Program
Files\PostgreSQL\13\data\pg_wal\0001000A0071
postgres.exe 30156 File C:\Program
Files\PostgreSQL\13\data\pg_wal\0001000A0071
postgres.exe 26976 File C:\Program
Files\PostgreSQL\13\data\pg_wal\0001000A0071
postgres.exe 26312 File C:\Program
Files\PostgreSQL\13\data\pg_wal\0001000A0074
...
Many wal files are used by multiple processes.
Andrus.
is regular postgres process which is
in idle state and waiting for ClientRead event. It has executed last
query an hour ago.
It looks like wal files are not released after usage.
Andrus.
and threads which use files in pg_wal
directory ?
Is there some utility for this or can restart manager or other API used
to create such log?
Andrus.
blocks to find changed
blocks to backup. So it should not cause permission denied errors after
every 10 seconds.
Andrus.
ORDER BY
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END DESC -- nulls first
Andrus.
files with .deleted extension deleted manually to save disk space
? May of them have dates before today.
Andrus.
021-03-06 19:46:19 EET checkpointer LOG: could not rename file
"pg_wal/000100070016": Permission denied
Should chekpointer process terminated to force it to use new setting. Is
it safe to kill it during database usage.
Andrus.
tel 67 files in pg_wal. Will
postgres remove .deleted files automatically or should I create windows
task which deletes them periodically ?
Andrus.
wal also contains files with .deleted extension like
0001000500B2.deleted
Andrus.
g_wal/00010005000000EB": Permission denied
2021-03-05 12:10:30.626 EET [4580] LOG: could not rename file
"pg_wal/0001000500EC": Permission denied
How to fix this ?
It looks like database is working normally, users havent reported any
issues.
Andrus.
better method.
>I don't follow what you mean.
Allow access to this table for single transaction only. Wait for
exclusive lock , do update/insert, release exclusive lock.
Andrus.
ated for this ?
After insert if other process inserts same key value, transaction still
ways. Should manual locking used or is there better method.
Andrus.
user
name. In this case one row should remain.
I can just ignore duplicate key error on commit but maybe there is some
better way not to cause error.
Andrus.
process working on that table.
It looks like other process has added same row during trancaction.
How to change script so that it works starting at Postgres 9.0 and does
not cause error in this case ?
Andrus.
his code from multiple processes to try
reproduce the error.
Andrus.
.
>Last inserted row data should remain.
I'm not understanding the above.
This table should contain last login time, user and ip address. It
should be updated by every process on login. For this old entry is
removed if it exists and new entry with same primary key is added.
Andrus.
Hi!
There is no other process inserting to this table?
There may be other processes in this server trying to insert same
primary key value (server name).
Last inserted row data should remain.
Andrus.
condition bug which was fixed in
later Postgres 9 version.
However this issue still exists in Postgres 13.1
Andrus.
(
set jit to off;
select ... from cartdata, ... where
set jit to on
)
But this cause syntax error.
How to turn off jit for specific subquery in Postgres 12 ?
Subquery is generated by EF Core and splitting it to multiple statements
is not possible.
Postgres upgrade is not planned.
Andrus
ck due to unexisting lo type.
C7=0 (bytea as logvarbinary is false) is used in connection string.
Andrus.
table.
Why it adds cast to lo type ? This type does not exist in Postgres
server and causes server error.
Andrus.
ybe this causes orphan large object creation by server or by odbc
driver. How to fix this ?
report table shoud not have lo type columns. No idea why this cast is
generated using psqlodbc
Andrus.
s a superuser?
> Do you see anything in the data field?
select * from pg_largeobject
running as superuser returs empty table with 3 columns:
loid, pageno and data
Andrus.
e200936761'
lo_export
sba=# \q
root@c202-76:~# ls -l large*
-rw-r--r-- 1 root root 0 veebr 2 10:45 large200936761
result file size is 0 .
Andrus.
can figure it out.
There is only one function , lo_get() in this page which returns data. I
tried
select * from lo_get(200936761);
select * from lo_get(200936762);
select * from lo_get(200936767);
Those queries returned one row containing one zero-length column lo_get.
Andrus.
gresql.conf to
lo_compat_privileges=on as temporary fix.
dumpuser has created backups of same two schemas for years without
issues. The issue starts to occur today.
Application does not create large objects. It uses bytea columns instead.
How to figure out what is this large object ?
Andrus.
class AS pc ON pa.attrelid = pc.oid
WHERE
atttypid = 'oid'::regtype
AND relnamespace in ( 'public'::regnamespace, 'firma74'::regnamespace )
AND attnum > 0;
It returs 0 rows.
Andrus.
152
200936767 30152
How to figure out what are large object with oids 200936761, 200936762
and 200936767 ?
Pd_dump throws error on first of them: 200936761
Andrus.
ion
It havent created any large objects.
What hasn't created large objects?
I
How to use this information to fix the issue ?
Do the pg_dump as user clusteradmin.
This works. However I need to allow non-supoeruser to create backup
also. How to do this ?
Andrus.
min WITH
LOGIN
SUPERUSER
INHERIT
NOCREATEDB
NOCREATEROLE
NOREPLICATION:
GRANT db1_owner, db2_owner, ... to clusteradmin;
It havent created any large objects.
How to use this information to fix the issue ?
Andrus.
https://www.postgresql.org/docs/12/catalog-pg-largeobject-metadata.html
instead.
select * from pg_largeobject_metadata
returns 3 rows:
Oid Lomowner
200936761 30152
200936762 30152
200936767 30152
How to find table and schema which is referenced by this ?
Andrus.
firma74
command line options are used.
You can query that to see what is there. I would not go about deleting
until you find what the large objects are for.
select * from pg_largeobject
returns empty table.
Database has approx 50 schemas and many thousands of tables.
Andrus.
w to find and delete all large objects in database ? Maybe it is
created accidently .
Using
PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
Andrus.
owner unchanged ?
Andrus.
"pg_default_acl_role_nsp_obj_index"DETAIL: Key (defaclrole,
defaclnamespace, defaclobjtype)=(30152, 186783649, r) already exists.
Should duplicate schema names accepted or should their usage throw
better error messages.
Andrus.
ot;testuser";
revoke all on kasutaja,kaspriv,logifail from "testuser" cascade;
grant select on kaspriv,kasutaja to "testuser";
grant update (eesnimi,
nimi,email,amet,islocked,telefon,language,vabakuup,kasilfirma) on
kasutaja to "testuser";
grant insert on logifail to "testuser";
Using
PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
Andrus.
makes this different from source position.
How to fix this so that 3 last tokens are returned in message like
Syntax error at or near ' i > )'
Andrus.
d products should moved first.
Only one product (quantity 1) should moved from each product code.
Using data abouve, there should be 3 shoes (producttype.desired) in shop
but are only 2 (sum(shop.quantity) for shoes).
Most sold shoe in warehouse is SHOE2
So SHOE2 should be moved to shop from war
acter,
select val('1,2%')
returns 0.
How to force it to return 1.2 ?
It should work starting from Postgres 9.0
Posted also in
https://stackoverflow.com/questions/63032072/how-to-create-function-returning-value-up-to-first-non-digit-decimal-charcater#63032126
Andrus.
-2020-06-19_00.log
How get yesterday log file from remote client application using postgresql
query ?
Using Postgres 12 in Debian.
Andrus.
t.oid = c.reltablespace
where
(pg_total_relation_size(c.oid)>>21)>0 and c.relkind!='t'
order by
pg_total_relation_size(c.oid) desc
Andrus.
s", not as
"root".
I tried
sudo --user=postgres pg_basebackup
but got error
could not change directory to "/root": Permission denied
Andrus.
postgres /var/lib/postgresql/12/main
/etc/init.d/postgresql start
How to create replication server ?
Andrus.
up so that cluster is tranferred over internet faster? Maybe it can transferred in compressed form over
internet.
Andrus.
/12/main
chmod --recursive --verbose 0700 /var/lib/postgresql/12/main
chown -Rv postgres:postgres /var/lib/postgresql/12/main
/etc/init.d/postgresql start
Andrus.
--recursive --verbose 0700 /var/lib/postgresql/12/main
chown -Rv postgres:postgres /var/lib/postgresql/12/main
/etc/init.d/postgresql start
In other words why do a pg_basebackup if you have a standby receiving WALs?
I dont receive WALs.
Andrus.
by setting
wal_keep_segments to a value large enough to ensure that WAL segments
are not recycled too early, or by configuring a replication slot for the
standby.
Will wal_keep_segments keep segments also if named replication slot is lot
used ?
Andrus.
Hi!
On 31. May, 2020, at 21:47, Andrus wrote:
replikaator@[unknown] ERROR: requested WAL segment 000102CF00E9
has already been removed
the message says it all. You need to copy the WAL file 000102CF00E9 and newer to the replica's pg_wal directory because
it has
wal_keep_segments= 360 # was 180
Will this allow replication to start after pg_basebackup ?
According to doc min_wal_size and wal_keep_segments both keep the minimum
number of wal segments for replication.
Why those parameters are duplicated?
Andrus.
specify recovery target time
for applying sql statements to base backup.
Is PITR recovery supported only using binary WAL files ?
Other limits can probably be solved.
Andrus.
(1) https://www.2ndquadrant.com/en/blog/icu-support-postgresql-10/
Andrus.
when new database is added or new tables are added to
database they will start replicate automatically ?
Will it require more powerful backup server to replay main server sql stream
from different databases.
Andrus.
alter table desktop alter baas type char(8) collate ucs_basic
fixes the issue.
Is this fix reasonable ?
What other issues may occur ?
Can base backup created in windows using pg_basecakup used in Linux without
such fix?
Andrus.
QL state: XX002
Andrus.
1 - 100 of 181 matches
Mail list logo
