t it wrong??
Z
--
Adrian Klaver
adrian.kla...@aklaver.com
To clarify:
We are not running the REFRESH. We are only running the materialised view. We
used to run the non-view query directly and always suffered from the conflict
with recovery error due to data changes on the replica server
n clusters that I recently upgraded to 14.
Which contradicts, "It is not really linked to the upgrade per say.".
On Mon, Feb 19, 2024 at 12:17 PM Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:
On 2/19/24 08:59, Johnathan Tiamoh wrote:
> Hello,
&g
Have I got it wrong??
Z
--
Adrian Klaver
adrian.kla...@aklaver.com
with your upgrading
procedures. By now you should know that the above does not qualify as
close to being a sufficient problem report. Respond with a complete
explanation of your setup and the commands run that got you into this
situation.
Kind regards
Johnathan T
--
Adrian Klaver
On 2/18/24 10:40, Adrian Klaver wrote:
On 2/18/24 10:30, Laura Smith wrote:
There's not bespoke SQL syntax for constructing a range. You must
use a function, something like
VALUES(p_event_id, tstzrange(p_start_time,p_end_time,'[)')) ...
Thanks all for your swift replies.
;2024-02-18', '2024-02-20', 'test', 'test
desc');
new_event_session
---
2
Lesson learnt !
Thanks again.
--
Adrian Klaver
adrian.kla...@aklaver.com
);
N.B. I'm calling from Go, the library does not natively support tstzrange,
hence the need to break-out the input parameters.
Thanks !
--
Adrian Klaver
adrian.kla...@aklaver.com
ault, whereas NOLOGIN is assumed when the command is spelled CREATE
ROLE."
It is entirely possible to CREATE USER ... WITH NOLOGIN ...
--
Adrian Klaver
adrian.kla...@aklaver.com
it yourself.
Sent from my iPhone
On Feb 17, 2024, at 09:46, Adrian Klaver wrote:
On 2/17/24 08:36, Kerr Livingstone wrote:
Are there version 6 binaries being generated for RHEL 7 ? The download page
indicates they should be there but the repo does not have a version 6 entry and
the
AC80 7D4F C89B
--
Adrian Klaver
adrian.kla...@aklaver.com
. Updating to RHEL 8 is not currently possible.
Define "...version 6 binaries".
Thanks
David
--
Adrian Klaver
adrian.kla...@aklaver.com
rg/docs/current/sql-altersequence.html>
such as |INCREMENT BY|.
"
You need to know how a sequence works and what can be modified.
David J.
--
Adrian Klaver
adrian.kla...@aklaver.com
On 2/15/24 09:00, Greg Sabino Mullane wrote:
On Thu, Feb 15, 2024 at 11:43 AM Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:
That is a mixed bag:
Ha! Good point. Our contrived example table does suffer from that, so
perhaps the test should be:
create table int_test(c1 i
TABLE
test=# select pg_relation_filenode('int_test');
pg_relation_filenode
--
70002
(1 row)
test=# select pg_relation_size('int_test');
pg_relation_size
--
368640
Cheers,
Greg
--
Adrian Klaver
adrian.kla...@aklaver.com
long-term row
identifier. A primary key should be used to identify logical rows.
"
But as you mentioned, it seems to be the effect of "fixed length data
type" which is why it's going to rewrite whole table even we just
increases the column length. Hope it won'
good deploy them to prod.
--
Adrian Klaver
adrian.kla...@aklaver.com
On 2/12/24 08:42, Adrian Klaver wrote:
On 2/11/24 15:13, ste...@gmail.com wrote:
Thanks, Adrian, for the suggestion, but same problem.
I've just tried to execute "ANALYZE" (on the whole database) after the
import of all the tables (with COPY) and before the other queries,
ng else at work,
see Tom Lanes suggestion. This may end up requiring installing auto-explain:
https://www.postgresql.org/docs/current/auto-explain.html
to get an EXPLAIN of the query.
Stefano
--
Adrian Klaver
adrian.kla...@aklaver.com
I don’t know if it could be due to some Postgres parameter.
Do you have any suggestions to solve this problem?
Run ANALYZE on the tables/database.
See:
https://www.postgresql.org/docs/current/sql-analyze.html
--
Adrian Klaver
adrian.kla...@aklaver.com
created by editing the output of a previous
-l operation. Lines can be moved or removed, and can also be commented
out by placing a semicolon (;) at the start of the line. See below for
examples.
Best bet is to dump the entire database.
thanks'
--
Domenico L.
--
Adrian Kl
.
unnamed.png
Regards
Siraj
--
Adrian Klaver
adrian.kla...@aklaver.com
On 2/6/24 10:18 AM, Adrian Klaver wrote:
On 2/6/24 10:11 AM, Ron Johnson wrote:
Currently, we use Object audit logging to capture all READ access to
columns FOO_1, FOO_2 and FOO_3 in table BAR.SCRABBLE. (They are the
three columns have PII data.)
Using what audit software?
Memo to
out using "grep -v" in a shell script
that runs hourly from cron, but I find that unsatisfactory.)
--
Adrian Klaver
adrian.kla...@aklaver.com
l giorno lun 5 feb 2024 alle ore 01:48 David G. Johnston
mailto:david.g.johns...@gmail.com>> ha
scritto:
On Sun, Feb 4, 2024 at 5:39 PM Adrian Klaver
mailto:adrian.kla...@aklaver.com>>
wrote:
>
> attribute_list
e not replicated. There is no
workaround for that, other than storing data in normal tables."
Regards,
Ken
--
Adrian Klaver
adrian.kla...@aklaver.com
ed individually.
--
Adrian Klaver
adrian.kla...@aklaver.com
gestions?
Bye and thanks'
Domenico
--
Adrian Klaver
adrian.kla...@aklaver.com
e. The bug list you filed
to above is not for packaging issues.
For that you need to visit:
https://redmine.postgresql.org/projects/pgrpms/
You will need to set up a community account first to access the Redmine
issue tracker.
--
Adrian Klaver
adrian.kla...@aklaver.com
Reply to list also.
Ccing list.
On 2/1/24 09:11, Johnathan Tiamoh wrote:
No. I have done any of this in the present cluster, I'm facing this issue.
Do you have some sort of replication or backup system running on this
cluster?
--
Adrian Klaver
adrian.kla...@aklaver.com
r procedures.
Did you have some difficulties with your instance lately? exceptional
intervention? backup restoration? failover?
--
Adrian Klaver
adrian.kla...@aklaver.com
On 1/29/24 11:35, Shaheed Haque wrote:
On Tue, 30 Jan 2024, 00:27 Adrian Klaver, <mailto:adrian.kla...@aklaver.com>> wrote:
On 1/29/24 10:12, Shaheed Haque wrote:
>
> Yes. But I was under the impression that the initial copy of logical
> replic
affected by WHERE clauses on the
publisher. Also if you have cascading publishers/subscriptions the
'original' data maybe upstream of the publisher you are comparing to.
Finally logical replication is generally not static so there is the
issue of determining a point in time for the check.
gical replication. Is that correct?
Thanks, Shaheed
>
> Thanks !
>
> Laura
>
>
--
Adrian Klaver
adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
--
Adrian Klaver
adrian.kla...@aklaver.com
e it intends to dump). It isn't
very large,
* but it can happen.
Or come up with way to capture the state of the data at the time of dump
and then compare to restored database. Something like Ron posted.
Thanks !
Laura
--
Adrian Klaver
adrian.kla...@aklaver.com
On 1/25/24 1:29 PM, Michael Nolan wrote:
I'm in the process of updating a server from postgresql 10.4 on Centos
7 to 16.1 on AlmaLInux 9, and am reminded of the old line about when
you're up to your necks in alligators, it is difficult to remember
that your goal was to drain the swamp.
We have
On 1/25/24 12:39 PM, Ron Johnson wrote:
On Thu, Jan 25, 2024 at 3:32 PM Adrian Klaver
wrote:
[snip]
Best guess is you are using a version of psql that is expecting the
socket to be somewhere else then where it actually is.
Is "permission denied" really the error you ge
then where it actually is.
Regards.
--
Adrian Klaver
adrian.kla...@aklaver.com
-time
Thanks !
--
Adrian Klaver
adrian.kla...@aklaver.com
data in a compromised state?
3) Have you considered partitioning? See here:
https://www.postgresql.org/docs/current/ddl-partitioning.html
Regards
Siraj
--
Adrian Klaver
adrian.kla...@aklaver.com
visena.com <https://www.visena.com>
<https://www.visena.com>
--
Adrian Klaver
adrian.kla...@aklaver.com
e a DB copy
and will be dropping the constraint to see what effect that has.
Cheers,
--
Adrian Klaver
adrian.kla...@aklaver.com
both
databases?
Do you have timestamp fields in each table that would show you when data
was entered or updated?
--
Adrian Klaver
adrian.kla...@aklaver.com
On 1/16/24 11:40 AM, Ron Johnson wrote:
On Tue, Jan 16, 2024 at 2:24 PM Adrian Klaver
wrote:
On 1/16/24 10:11 AM, Ron Johnson wrote:
On Tue, Jan 16, 2024 at 12:40 PM Adrian Klaver
wrote:
On 1/16/24 09:20, Ron Johnson wrote:
> Some RDBMSs have CREATE AL
On 1/16/24 10:11 AM, Ron Johnson wrote:
On Tue, Jan 16, 2024 at 12:40 PM Adrian Klaver
wrote:
On 1/16/24 09:20, Ron Johnson wrote:
> Some RDBMSs have CREATE ALIAS, which allows you to refer to a
table by a
> different name (while also referring to it by the origina
On 1/16/24 10:55 AM, Yongye Serkfem wrote:
Hello Engineers,
I trust you are all doing well. I need help on how to improve the
speed of pg_dump. I took a pg_dump on a 1TB database, which took
almost a whole day. I used this command: "pg_dump -U postgres -d
dynamic -f /backups/." Also, how do I
On 1/16/24 09:59, Ron Johnson wrote:
Performance-killing alternatives are not really altternatives.
Unless it is the only one that solves your problem.
--
Adrian Klaver
adrian.kla...@aklaver.com
On 1/16/24 09:23, Dominique Devienne wrote:
On Tue, Jan 16, 2024 at 6:10 PM Adrian Klaver <mailto:adrian.kla...@aklaver.com>> wrote:
Technically, it's still a ROLLBACK, so that is indeed the only recourse.
Actually ROLLBACK TO:
https://www.postgresql.org/docs/current/sql-ro
also be helpful to reply with the table definitions for the
tables. If that is not possible then at least whether there is an index
on the FK reference in the child table(s)?
Cheers
Jim
--
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London
--
Adrian Klaver
adrian.kla...@aklaver.com
You would get a conflict with the existing table MTQRY.sometable.
Though I noticed you have both MTQRY and MYQRY referring to the same
thing, I think.
--
Adrian Klaver
adrian.kla...@aklaver.com
;
--
Adrian Klaver
adrian.kla...@aklaver.com
On 1/16/24 09:04, Dominique Devienne wrote:
On Tue, Jan 16, 2024 at 5:07 PM Adrian Klaver <mailto:adrian.kla...@aklaver.com>> wrote:
On 1/16/24 00:06, Dominique Devienne wrote:
> On Mon, Jan 15, 2024 at 5:17 AM veem v mailto:veema0...@gmail.com>
> <mailt
was a hardware failure.
Thanks
Bablu Nayak
--
Adrian Klaver
adrian.kla...@aklaver.com
a transaction that allows all
commands that are executed after it was established to be rolled back,
restoring the transaction state to what it was at the time of the
savepoint."
--
Adrian Klaver
adrian.kla...@aklaver.com
this issue.
Thanks in advance.
---
On 2024-01-15 6:22 p.m., Adrian Klaver wrote:
On 1/15/24 14:38, Arbol One wrote:
Netbeans 20. Sorry, my bad.
And the rest of the questions?
On 2024-01-15 4:48 p.m., Adrian Klaver wrote:
On 1/15/24 13:33, Arbol One wrote:
I am working
On 1/15/24 14:38, Arbol One wrote:
Netbeans 20. Sorry, my bad.
And the rest of the questions?
On 2024-01-15 4:48 p.m., Adrian Klaver wrote:
On 1/15/24 13:33, Arbol One wrote:
I am working on an application using NB-20,
PG-psql (16.1 (Debian 16.1-1.pgdg120+1), server 15.5 (Debian
15.5-1
is it just me; I wonder.
The question is, how can I tackle this issue.
Thanks in advance.
--
Adrian Klaver
adrian.kla...@aklaver.com
Jan 14, 2024, at 8:50 AM, Adrian Klaver wrote:
On 1/13/24 21:52, Lan Xu wrote:
With CREATE EXTENSION unaccent, I’m getting the following:
ERROR: extension "unaccent" has no installation script nor update path for version
“1.0"
But I can find it under /usr/share/pgsql/extension/un
M: +905452120762
E: eyup.li...@turk.net
-Original Message-
From: Adrian Klaver
Sent: Monday, January 15, 2024 7:10 PM
To: Eyüp Liste ; pgsql-general@lists.postgresql.org
Subject: Re: data migration using EXTENSION tds_fdw
[UYARI] Bu ileti TurkNet dışından gönderildi. Lütfen gönderen adresleri ko
On 1/15/24 09:26, arun chirappurath wrote:
Hi Adrian,
\d shows the tables and this id which is a sequence.
What I asked for was the output of:
\d automobile
Add that output to your reply.
Regards,
Arun
--
Adrian Klaver
adrian.kla...@aklaver.com
this.
Do you have psql(https://www.postgresql.org/docs/current/app-psql.html(
available?
If so in psql what does:
\d automobile
return?
Visually both statements are alike
Table is in public schema.
Thanks,
Arun
--
Adrian Klaver
adrian.kla...@aklaver.com
me, it's basically this:
with t as (
INSERT INTO phone_book
VALUES ( 'Peter Doe', '555-2323' )
returning *
)
select *
from t;
There is also transition relations:
https://www.postgresql.org/docs/current/sql-createtrigger.html
though that involves
aracter
set." would seem to be the issue.
Therefore what is the character set used in the?:
1) The SQL Server database.
2) The Postgres database
Please help me about this issue.
Best regards
*turknet-logo* <https://turk.net/>**
*Eyüp Liste*
Technology
System An
!
What OS and version?
What Postgres version?
How did you install Postgres?
Lan
--
Adrian Klaver
adrian.kla...@aklaver.com
narrower question:
/"How does one capture output from pg_recvlogical and pipe it back into
the database with psql?"/
I don't know. For those that might a self contained example of what you
want to achieve would be a good start. I doubt that many will look at
the screencast.
Be
ine experience with other tools, this
/should/ work. I should be able to send the output to stdout
and then redirect it to a file. It surprises me that I cannot.
Anyway, thanks!
Best,
David
--
Adrian Klaver
adrian.kla...@aklaver.com
--slot=test --start -f - | cat >
sample.txt
FWIW, this is demonstrated in this screen-cast
<https://asciinema.org/a/631166>. I suspect it has something to do with
the flushing of buffers, as other people have suggested.
Thanks!
David
On Fri, Jan 12, 2024 at 1:48 PM Adrian Klaver &
pdate my mental model of how this is supposed to work.
Based on my current (flawed) mental model built up from command line
experience with other tools, this /should/ work. I should be able to
send the output to stdout and then redirect it to a file. It surprises
me that I cannot.
Anyway, t
SS.US0 TZH:TZM');
end;
$function$
select ToDatetimeOffset(now(), 'Europe/Amsterdam');
todatetimeoffset
2024-01-12 18:32:02.5486820 +01:00
--
Adrian Klaver
adrian.kla...@aklaver.com
n that an `Â` (which corresponds
to `C2`) is seen before each intended character. Looks like UTF-8
encoded data is mistakenly interpreted as Latin-1 (ISO-8859-1) or
Windows-1252, where each byte is treated as a separate character.
Please advise. Thank you very much.
Regards,
Kiran
--
Adri
(current_timestamp, '-MM-DD HH24:MI:SS.US0 TZH:TZM');
todatetimeoffsetnl | to_char
+
2024-01-12 09:42:44.1989210 +01:00 | 2024-01-12 08:42:44.1989210 +00:00
(1 row)
Regards,
Alban Hertroijs.
--
Adrian Klaver
adrian.kla...@aklaver.com
021986
(1 row)
test=# select to_char(now() AT TIME ZONE 'Europe/Amsterdam', '-MM-DD
HH24:MI:SS.US0 TZH:TZM') ;
to_char
2024-01-12 00:44:57.5421420 +00:00
(1 row)
You end up with string that does not the corre
mezone;
TimeZone
--
UTC
(1 row)
test=*# commit ;
COMMIT
test=# show timezone;
TimeZone
--
UTC
(1 row)
Frankly, I do hope that you’re right here, that would make my work easier.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you&#x
check tomorrow.
Frankly, I do hope that you’re right here, that would make my work easier.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
--
Adrian Klaver
adrian.kla...@aklaver.com
On 1/11/24 08:48, Adrian Klaver wrote:
On 1/11/24 08:04, Alban Hertroijs wrote:
The drawback, as mentioned, being that we need to maintain those
functions in each deployment, which is a bit of a hassle (albeit a
minor one) because we need to customise both the TDV side and the
PostgreSQL
approach may have to suffice.
The reason I decided to ask on the ML is that I'm finding it hard to
believe that this transformation would be this difficult, so I expect
that I must be missing something.
Regards,
Alban Hertroys
--
Adrian Klaver
adrian.kla...@aklaver.com
in the caching database before we
can use them, while we have several environments where they would apply
(DEV, pre-PROD, PROD).
--
Adrian Klaver
adrian.kla...@aklaver.com
.
Look in:
D:\Users\dbarron-aas\AppData\Local\Programs\pgAdmin 4\runtime
for
pgAdmin4
Then double click on that. I'm guessing it will be 8.1.
Per Craig's comment I'm betting that the icon/shortcut you are clicking
on currently is pointing back at the 7.x version.
--
Adrian Kl
On 1/10/24 11:37 AM, David Barron wrote:
I am running Windows 10.
I downloaded the Dec 14 release from pgadmin.org
I installed by double-clicking on the executable I downloaded and
running through the installation wizard without making any changes.
Well that looks good to m
On 1/10/24 10:58 AM, David Barron wrote:
On 1/10/24 10:47 AM, David Barron wrote:
Is there a problem with the installation of 8.1? I’ve installed
it 3 times but when I bring it up it still says I’m at 7.8. The
Help > About menu shows 7.8 as well.
What OS are you using?
Where di
On 1/10/24 10:47 AM, David Barron wrote:
Is there a problem with the installation of 8.1? I’ve installed it 3
times but when I bring it up it still says I’m at 7.8. The Help >
About menu shows 7.8 as well.
What OS are you using?
Where did you get pgAdmin from?
How did you install it?
There is likely to be more folks there that could point you in the right
direction.
That’s why I wanted to find out if the software was compatible with my
system.
Cheers
Innocent
On Mon, 8 Jan 2024 at 16:42, Adrian Klaver <mailto:adrian.kla...@aklaver.com>> wrote:
On 1/8/24 0
compatible with my MacBook Air 2012, iOS
version 10.15.
Where are you getting the software from?
Show the commands you are using to install.
What are the error messages you are getting?
Looking forward to your reply
Cheers
Innocent
--
Adrian Klaver
adrian.kla...@aklaver.com
he argument list (in case there's >1 with
same name, diff arg list), but '()' doesn't work. Does this have to do
with the returned trigger type ?
--
Adrian Klaver
adrian.kla...@aklaver.com
On 1/3/24 9:50 AM, arun chirappurath wrote:
On Wed, 3 Jan, 2024, 23:03 Adrian Klaver,
wrote:
On 1/3/24 09:24, arun chirappurath wrote:
> Hi Adrian,
>
> Thanks for your mail.
>
> Is this for all tables in the database or a subset? Yes
Yes all tab
data do you
want to produce?1Gb and 1Gb test data.
If the source data is 1GB and the test data is 1GB then there is no
sampling, you are using the data population in its entirety.
On Wed, 3 Jan, 2024, 22:40 Adrian Klaver, <mailto:adrian.kla...@aklaver.com>> wrote:
On 1/2/24 23
foreign key relationships?
What are the sizes of the existing data and what size sample data do you
want to produce?
Regards,
Arun
--
Adrian Klaver
adrian.kla...@aklaver.com
cs/pgadmin4/latest/backup_dialog.html>
where it mentions:-
Use the Encoding drop-down listbox to select the character encoding
method that should be used for the archive.
--
Adrian Klaver
adrian.kla...@aklaver.com
without connecting to the database.
Then use that statement directly in the database.
b) If SQLAlchemy can reach the database then csvkit should be able to.
https://csvkit.readthedocs.io/en/latest/
<https://csvkit.readthedocs.io/en/latest/>
Regards
Arun
--
Adrian Klaver
adrian.kla...@aklaver.com
On 1/2/24 08:15, Dominique Devienne wrote:
On Tue, Jan 2, 2024 at 5:09 PM Adrian Klaver <mailto:adrian.kla...@aklaver.com>> wrote:
On 1/2/24 07:24, Dominique Devienne wrote:
> pg_has_role() from
> https://www.postgresql.org/docs/current/functions-info.
https://rhaas.blogspot.com/2023/01/surviving-without-superuser-coming-to.html
Thanks, --DD
--
Adrian Klaver
adrian.kla...@aklaver.com
sively in our PostgreSQL-based apps,
and I've read a lot about them, but at times I feel I'm missing something.
Thanks, --DD
--
Adrian Klaver
adrian.kla...@aklaver.com
.
Best regards,
--
Adrian Klaver
adrian.kla...@aklaver.com
ll therefore consider using a database change management system instead
(e.g. sqitch, suggested by Adrian) and defining there what should happen when
the schema name is changed, including the names of all functions whose
search_path is to be changed.
Many thanks again
Wilma
--
Adrian Klaver
adrian.kla...@aklaver.com
have a mismatch between the file format
of the dump file and the version of pg_restore you are using to attempt
to restore that file. Until that gets sorted out you will not get any
further.
--
Adrian Klaver
adrian.kla...@aklaver.com
server I have taken backup*.**
5) Do you have more then one version of Postgres installed? *14.10 &
16.1 in server which I have taken backup*
**
*From:*Adrian Klaver
*Sent:* 30 December 2023 00:45
*To:* ankit.si...@nest-is2.com; pgsql-general@lists.postgresql.org
*Subject:* Re: Need help
On 12/29/23 6:02 AM, ankit.si...@nest-is2.com wrote:
In Ubuntu 22.04LTS when we try to restore the database it's giving the
error – pg restore unsupported 1.15
Needs a lot more information:
1) Postgres version being restored to.
2) The complete pg_restore command.
3) The complete error
he advisability of
changing defaults without testing what that does to the end user or
notifying the end user.
On Fri, Dec 29, 2023 at 7:23 AM Tom Lane <mailto:t...@sss.pgh.pa.us>> wrote:
Junwang Zhao mailto:zhjw...@gmail.com>> writes:
> On Fri, Dec 29, 2023 at 4:4
go back to pglz.
On Thu, Dec 28, 2023 at 11:23 AM Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:
On 12/28/23 09:13, Sean Flaherty wrote:
> Follow-up:
> Working with AWS, we found that starting in RDS Postgres 15, the
> default_toast_compressi
on disk.
I can confirm I see the same results using Postgres 16 installed from
the PGDG repo on Ubuntu 22.04. That the lz4 data size is greater then
the pglz data size.
Kind regards,
Sean
--
Adrian Klaver
adrian.kla...@aklaver.com
On 12/26/23 22:41, Kirk Wolak wrote:
On Mon, Dec 25, 2023 at 1:47 PM Christophe Pettus <mailto:x...@thebuild.com>> wrote:
> On Dec 25, 2023, at 10:44, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:
> Functions with same name in different sch
401 - 500 of 3961 matches
Mail list logo
