58B53A4DC9A%40ntex2010i.host.magwien.gv.at
This might be a false positive hit or not, I am not certain.
Maybe the check is not required for AFTER triggers.
Anyway, the problem can be avoided by running
SET CONSTRAINTS resource_type_fk IMMEDIATE;
right before the CREATE INDEX, so I do
g "synchronous_commit = off",
and that is crash-safe.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ich is as good as it gets.
For an exact answer for your specific system, run a simple benchmark.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
; types ?
This has nothing to do with the size; I guess the answer is the same as
above. One proble that I can see immediately is that primary and
standby don't share the same OIDs, yet every large object is identified
by its OID. So I think this is a fundamental problem that cannot be
so
UNIX,
you could use PAM's LDAP module to do what you want.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
QL system and its name?
Yes.
You only have to make sure that the original license text is included
in your license. This does not limit what you are allowed to do with
the software.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
, make sure that
"autovacuum_vacuum_cost_delay" is small enough so that autovacuum
finishes quickly.
- For tables that receive only INSERTs, schedule a regular VACUUM
with "cron" or similar. Unfortunately, PostgreSQL isn't very smart
about vacuuming insert-only tables.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
oesn't
interfere with your other activities.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ch
permission concept and row level security.
- The name of the user might as well be an e-mail address, as long
as it does not exceed 63 bytes.
- Make use of user groups and grant privileges on that level rather
than to the individual users.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
SET id = 1 WHERE id = 42
If these statements are executed at the same time on two
databases and then replicated, DB 2 will end up with one row
more in the table than DB 1.
Try to find a solution with a single master database.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
is in
the WHERE clause can use the index.
If partitoining is really what you need, you could create the primary
key on the partitions and not on the partitioned table.
That won't guarantee you global uniqueness, but you could rely on
a sequence to do that.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
but not yet committed on the standby.
You should use "pg_rewind" on the failed primary if you want to use
it as new standby for the promoted server.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
COMMIT returns.
If there is a failure after the first step completed, the
transaction will be committed locally, but not on the standby.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
he backup.
pg_archivecleanup <*.backup file>
can make the task simpler.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ick the better plan using just the one index on
> id_column_2 (aside from re-writing the query).
Extended statistics will tell PostgreSQL that it is very unlikely
that the first condition will contribute significantly, but that
is no proof that the condition can be omitted, so the optimizer
ca
es, there is a bug somewhere. FWIW, it works on my Linux system.
To get a case insensitive collation you'd have to use something like
LOCALE = 'de-DE-u-ks-level2'
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
so it cannot rewrite indexes
to take advantage of these improvements.
There is no incompatibility involved.
You can always REINDEX some indexes later.
Now that we have REINDEX CONCURRENTLY, it shouldn't hurt as much.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ale='en-US-u-ks-level2', deterministic=false
);
The name of the locale defines it.
My blog post can give a simple introduction:
https://www.cybertec-postgresql.com/en/icu-collations-against-glibc-2-28-data-corruption/
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
to use lots of memory for certain operations, which is not
limited by "work_mem".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
27;$.k1.list ? (@.type() == "number")'), --
lax mode unwraps the array
jsonb_path_exists(data, 'strict $.k1.list ? (@.type() == "array")') --
strict mode doesn't
from sample;
data | jsonb_path_exists | jsonb_path_exists
-+---+---
{"k1": {"list": [1, 2, 3]}} | t | t
(1 row)
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ill know that you are executing a
cursor
and will plan its queries differently: it will assume that only
"cursor_tuple_fraction"
(default 0.1) of your result set is actually fetched and prefer fast startup
plans.
If you don't want that, because you are fetching batches as fast as you can
without
lengthy intermediate client processing, you might want to set the parameter to
1.0.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ease have some understanding.
That said, I think it is reasonable that a PostgreSQL JSON function
behaves in the way that JSON users would expect, so here is my +1 for
interpreting an SQL NULL as a JSON null in the above case, so that the
result of the above becomes
{"a": null, "b": 2, "c": 3}
-1 for backpatching such a change.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
many people who
use JSON in the database are more savvy with JSON than with SQL
(they might not have chosen JSON otherwise), so I agree that it makes
sense to change this particular behavior.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
tion is: there is any chance/procedure to recover the databases?
>
> As usual, the last backup has "a few" weeks. So, this dump can be used to
> recover the recent data?
I'd cut the losses and take the backup.
You'd need a specialist to recover anything there.
Yours
ace(array[1,2,3],2,NULL);
> array_replace
>---
> {1,NULL,3}
>
> The returned value is not NULL. Why the inconsistency between the array
> type and json type? Are there any cases other than json where the entire
> compound value is set to NULL as a re
without spaces.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
you to create a copy of a string in an
explicitly specified memory context.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
esql.conf" on
the standby, your query will be given more time to complete.
This will, however, cause replay of the replicated changes to be delayed.
Yours,
Laurenz Albe
truncation.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
conflicts, *or* it can be used to run resource
intensive reading queries, in which case application of the transaction
log should be delayed.
Don't try to use a standby for both - use two standby servers.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
a single table, run
ALTER TABLE x SET (autovacuum_vacuum_cost_delay = 2);
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ine = 'latest'" so that recovery will
follow the timeline jumps.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
e suggest how should I proceed with major version upgrade in this case.
You are experiencing catalog corruption.
Try stopping the cluster and starting it with
postgres -P -D /path/to/data/dir
Then connect and run
REINDEX TABLE pg_catalog.pg_class;
That should fix the corrupted index.
If you have data corruption, you shouldn't use pg_upgrade to upgrade.
pg_dumpall / psql is the way to go.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
m;
> If I could avoid using pg_get_expr() I'd have one more concern left
> since the original query returns
>
> nextval('public.horde_alarms_id_seq'::regclass)
>
> while interrogating information_schema.columns returns
>
> nextval('horde_alarms_id_seq'::regclass)
That is because of your "search_path" setting.
Schemas are only shown if they are *not* on your "search_path".
You can set "search_path" to an empty string to always get the schema.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
, BUFFERS) output for the query.
Including a list of parameters you changed from the default is helpful too.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
can help to get back the transactions?
This information is not contained in the WAL.
You'll have to restore a backup.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
s can be located in the
same tablespace and vice versa.
Think of "database" and "schema" as a logical separation in SQL.
You cannot backup and restore an individual tablespace, only the
whole cluster.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ople that our new postgres env is fine.
> So, ... any idea ?
Sure.
Restore a backup and perform point-in-time-recovery.
Then extract the data you need.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
th the default tablespace.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
a lower level using striping).
- Put some tables or indexes faster and some on slower storage.
- Provide a size limit for a table or a database.
- Have your temporary files on a different file system.
In a virtualized environment, you never want tablespaces.
Yours,
Laurenz Albe
--
Cyb
the permissions of the directories on the path.
If you can write to the directory, you will be able to create
a tablespace there.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
What keeps you from explicitly dropping the table right before
you commit?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ESTAMP .
That means you know which table and which timestamp.
So restore the PostgreSQL cluster to that timestamp, connect
and SELECT from the table.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ould not get triggered on passive2.
>
> so passive2 can be used as archival db.
> if active1 fails then , passive1 will become active2.
You would need logical replication for passive2.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
tables, and then pitr to the second timestamp
> so as to be able to script a kind of "diff" between the 2 to get what I want.
Sure, you can do that.
The description of what you wanted to do was rather unclear, all I could
make out is that you want to query AS OF TIMESTAMP.
Yours,
64');
>
> [...]
>
> -- explain analyze
> SELECT *
> FROM "cp"."Repro" x where cp.make_tsvector(x) @@ 'sup:*'::tsquery
One possibility is that there ar just too few rows in the table.
SET enable_seqscan = off;
and then try again.
If that is not
, manually fix the corruption
by deleting rows that violate the constraint.
Then create a new PostgreSQL cluster with "initdb", dump the original
database with "pg_dumpall" and restore it to the new cluster.
That should get rid of all data corruption.
Make sure you upgrade to 10.11.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ndex.
I cannot quite follow.
We have seen that the query can use the index by setting "enable_seqscan = off",
but that PostgreSQL prefers to use a sequential scan because the table is small.
If the table were bigger, PostgreSQL would prefer the index scan.
Are your concerns hypotheti
could be something to investigate.
Right, Slony is the way to go, since pg_upgrade doesn't support 8.3.
I would upgrade to a version more recent than 9.6.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
AS opf ON amp.amprocfamily = opf.oid
JOIN pg_am ON opf.opfmethod = pg_am.oid
WHERE pg_am.amname = 'hash'
AND amp.amprocnum = 1;
Or at least there would have to be a fat warning in the release notes
to reindex hash indexes.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
have to install postgres on a network with NO internet access.
https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7.9-x86_64/
https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-6.9-x86_64/
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
cal replication
> can be a bit finicky, so I'd love to have some validation of the
> general approach before I go down this road.
I don't think that will work.
Any changes on A that take place between step 1 and step 3 wouldn't be
replicated to C.
You'd have to suspend all data modification on A in that interval.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
t; set to "off", and you must catch the error
in your code and issue an explicit rollback.
Maybe you should have a look at your transaction management in general, because
it is very important that COMMIT is issued once any transaction is completed.
COMMIT on an aborted transaction will
t "default_transaction_read_only = on",
but that will only work if the clients don't override it explicitly.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ick, I would restart PostgreSQL, which should get
rid of any possible memory corruption.
Then perhaps the anti-wraparoung autovacuum can succeed.
This autovacuum would also block you, but you should let it finish, since
it is an important system task.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
y dump/restore.
It may, however, be caused by the following:
- Upgrading the operating system where PostgreSQL is running to a
different glibc.
- Streaming replication between machines with different glibc version,
and failing over to the standby.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Thu, 2020-01-16 at 12:09 +0100, Arnaud L. wrote:
> is it OK to set wal_level to minimal on the subscriber side of the
> logical replication ?
Yes, if you don't want physical backups.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ld still shutdown the subscriber and take an offline physical
> backup, right ?
Yes, that is what I mean.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ts" high enough on the primary for safety.
Run "pg_basebackup" on the standby.
Create a "recovery.conf" on the standby that has "primary_conninfo" set
and "standby_mode = on".
Start the standby and wait until it has caught up, then switch over.
Yours,
Laurenz Albe
ch down time, use a smaller value.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
l be happy to make a fix my first contribution to postgresql.
> :)
>
> Here are the docs:
>
> (https://www.postgresql.org/docs/10/sql-altertable.html)
We'd be happy about a documentation patch.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Thu, 2020-02-06 at 03:54 +0100, Vik Fearing wrote:
> I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.
+1
Yours,
Laurenz Albe
ny feedback are welcome!
I don't want to detract from this, but from PostgreSQL v10 on you can use
ICU collations with the "kn-true" variant to have natural sort order.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ssage flow and see
where the time is spent.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
27;t forget to ANALYZE.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
What to do to enable this extension?
>
> In windows, this works fine. Issue is when installing under Linux only.
You forgot
yum install postgresql11-contrib
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
a better solution.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
d to make that as slave for new
> primary.
> 3. Once slave (original primary) is caught up with primary do failback
> 4. Repeat steps #1-#3 to make failed over instance slave again.
Or you use Patroni and simply say
patronictl.py switchover --candidate db2 --scheduled '2020-03-06
ical) file system is (Oracle calls it ASM / tablespace, but it is
still a file system).
Yours,
Laurenz Albe
;table_owner" role?
If you don't want user B to be able to drop user A's table, why don't
you have each user be the owner of his tables?
Yours,
Laurenz Albe
stem is (Oracle calls it ASM / tablespace, but it is
> > still a file system).
>
> Why isn't the filesystem (or RDBMS) throwing checksum errors? This was
> standard stuff in legacy Enterprise RDBMSs 20 years ago.
Checksums are nice for telling you that your storage is screwed.
They don't fix the problem.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
become corrupted, the others are not immediately
affected. That's the point of a shared-nothing architecture.
Of course, if the corrupted database is the primary, corruption can
eventually spread to the others.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
system backup, you will have what you had before.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
better to catch
errors whenever you perform a database operation and retry the operation if the
error indicates that you have lost the connection?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
in PostgreSQL, and a
number of such bugs have been fixed over the decades, so you might
be happier with a less ancient version.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
r the same purposes,
but in a supported fashion.
Yours,
Laurenz Albe
hink that is good for large
data sets.
Setting "work_mem" and "temp_buffers" high, you can use all the RAM
you have, and if it still is not enough, it spills to disk rather than die.
Yours,
Laurenz Albe
k route to the primary
server to lead through the virtual interface.
But I don't see any reason to do that.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
point to specific place in the code, please?
Look for the int4hashfast, texthashfast and similar functions in
src/backend/utils/cache/catcache.c
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
d like
Kerberos. Then you can enforce the password restrictions there.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Tue, 2020-03-24 at 17:50 -0500, Chris Morris wrote:
> Is it even possible to use PG partitioning across a Foreign Server?
I am not certain what you mean, but you can have foreign tables as partitions
of a partitioned table. The partitions won't be processed in parallel though.
Yours,
d be to use pg_dump to move the schema definitions over
while preserving the "public" schema, and then use
ALTER ... SET SCHEMA newschema;
on all objects.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
don't have an explicit check for making
> sure the file system is present in the start script. Thanks for the tip.
If that is an NFS mount, make sure it is "fg", not "bg".
Also, check that your startup script simply fails if the file system is not
mounted yet, rather than automatically running "initdb".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ment this ?
You'd have to use an OpenSSL library with compression support enabled.
But that will improve speed only if your workload is network bound,
not CPU bound (in which case performance will suffer).
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
>
> Should I change OpenSSL installed from standard repository in Debian server
> or can it changed only for PostgreSql.
> How ?
I don't know if Debian's binaries are built with compression support,
probably not. You can build OpenSSL yourself and make PostgreSQL
u
ss', a.class_grade
FROM all_classes a
WHERE EXISTS (SELECT 1 FROM students s1
WHERE a.class = s1.class
AND s1.class = 'math');
I deliberately ignored that the condition could be simplified substantially.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
xt,
> yhik =unnest(xpath(
>
> '/E-Document/Document/DocumentItem/ItemEntry/BaseUnit/text()',x))::text
> from t
>
> throws error
>
> set-returning functions are not allowed in UPDATE
>
> How to fix this ?
Simply replace
SET col = unnest(
tion to use a partitioned
table and load the new data in a new partition. Then you can add the index
after loading and only then you turn the new table into a table partition.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Fri, 2020-04-03 at 00:59 -0400, AC Gomez wrote:
> Granted. But we are where we are, so I'm assuming this is going to be hand to
> hand combat.
Yes, unfortunately there is no better way.
But DROP ROLE will produce messages that help you along.
Yours,
Laurenz Albe
--
Cyber
his ?
>
> If disk is nearly full, master should stop additional files creation (and
> maybe stop or disable replication slot).
> Postgres 12 in Debian 10 is used.
That's why you use monitoring.
Never use replication slots without monitoring replication (or at least the
disk spa
of WAL.
Essentially, you are asking, "I have a cup that fits 20 ounces, now
I pour in a gallon, and it overflows. How can I prevent that?"
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
rce to stop replication slot in master if it is in use ?
You don't.
You reserve enough disk space that WAL can grow for a while before
filling the disk. Any you need to trigger human intervention with
monitoring.
If you prefer replication to fail silently, don't use replication
s currently executing will continue to do so, but queries planned
after the ALTER SERVER will use the new values.
Sometimes plans are cached, but all cached plans that involve the changed
server will be invalidated and re-planned after the ALTER SERVER.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
as no idea which standby server needs which WAL information.
All you can tell is where the currently connected standby servers are:
SELECT pg_walfile_name(flush_lsn) FROM pg_stat_replication;
About your third question, you *never* manually mess with the files in pg_wal.
The server does that.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
If you have to worry about that
much disk space, you're must be running PostgreSQL on a toaster.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
nce will break loose.
The better solution is to use a connection pool. If your application doesn't
have one, use pgBouncer.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
I have seen databases servers go down because of such events, and
the problem is the high number of connections, even if they are idle
most of the time.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
an ACCESS SHARE lock on the table, which conflicts only with
ACCESS EXCLUSIVE.
So creating the foreign key works, but dropping it hangs when the triggers are
dropped.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
grant permissions to a role that you
plan to drop.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
covery to restore such a backup.
> And, coming back to my question, can older WAL in wal_archive/ be
> removed without any risks if the daily
>
> pg_wal.tar.gz
> base.tar.gz
>
> are moved to a safe place?
Yes, because "-X stream" is the default for "pg_basebackup".
"pg_wal.tar.gz" will contain all the WAL necessary to recover the backup, so
if you unpack its contents into the "pg_wal" subdirectory of the unpacked
base backup, you can simply start the server, and it will recover to the end
of the backup.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
101 - 200 of 1221 matches
Mail list logo
