7;s tighter nowadays, but I really doubt that
> it's exact-to-the-kilobyte-at-all-times.
>
In this case, the total volume size was 60GB and we had the parameter set
to 58GB but I imagine that can still be overwhelmed quickly. Maybe we
should target a 20% buffer zone? We have wal_kee
deals with
situations where the replication slot lag is a factor?
Don.
--
Don Seiler
www.seiler.us
led)
Does this mean that downgrading libpq wouldn't help? He'd have to downgrade
openssl instead (or wait for a fix from somewhere upstream)?
Don.
--
Don Seiler
www.seiler.us
ences or if this is a
known issue?
--
Don Seiler
www.seiler.us
On Tue, Oct 3, 2023 at 10:27 AM Don Seiler wrote:
> On the source (PG 12.15) instance, we have bumped max_replication_slots
> and max_wal_senders to 50, and max_sync_workers_per_subscription to 10.
>
Forgot to note that on the subscriber (PG 15.4) instance,
max_sync_workers_per_subscr
20 replication
slots created (out of 50 max), 3 of which are the subscriptions and the
rest are the tablesyncs workers.
So I'm trying to make sense of why it would tell me to increase
max_replication_slots when I don't appear to be anywhere near the max.
--
Don Seiler
www.seiler.us
es VM sizes. There's
obviously going to be a little extra HugePages that goes unused, but these
VMs are dedicated for postgresql usage and shared_buffers_size defaults to
25% of VM memory so there's still plenty to spare. But we use this so we
can configure vm.nr_hugepages at deployment time via Chef.
Don.
--
Don Seiler
www.seiler.us
is from a different pid, maybe some kind of cleanup process? It
was doing this for what seemed like all of the sync slots:
2023-08-03 18:47:47.975 UTC [3303] migrator@foo - sub01 ERROR: replication
slot "pg_19742_sync_17238_7263122209699118815" does not exist
2023-08-03 18:47:47.975 UTC [3303] migrator@foo - sub01 STATEMENT:
DROP_REPLICATION_SLOT pg_19742_sync_17238_7263122209699118815 WAIT
--
Don Seiler
www.seiler.us
sync workers in action so this was a big surprise. Is this
expected behavior?
--
Don Seiler
www.seiler.us
On Wed, Jun 7, 2023 at 4:30 PM Jeremy Schneider
wrote:
> On 6/7/23 2:12 PM, Don Seiler wrote:
> > On the logical replication front, the concern is with the initial data
> > import that happens when the subscription is created (by default). I
> > know that you can tell th
not. I know to only use the minimal
indexes required on the destination side (ie identity-related indexes) and
omit other indexes and constraints until after the data is loaded, but that
is true for either method.
Thanks,
Don.
--
Don Seiler
www.seiler.us
ondering if I'm on the right trail or if there is a much firmer
explanation for what happened.
Thanks,
Don.
--
Don Seiler
www.seiler.us
On Mon, Mar 29, 2021 at 5:22 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Mar 29, 2021 at 3:20 PM Adrian Klaver
> wrote:
>
>> On 3/29/21 3:00 PM, Don Seiler wrote:
>> >
>> > I'm wondering if this is expected behavior that PG us
logic was used in an UPDATE and
ended up locking all rows in a 5M row table and brought many apps to a
grinding halt. Thankfully it was caught and killed before it actually
updated anything.
Thanks,
Don.
--
Don Seiler
www.seiler.us
#x27;m
wondering if there is risk or harm in running an UPDATE pg_database command
on postgres/template0/template1 as needed and re-indexing afterward.
--
Don Seiler
www.seiler.us
. We shouldn't
be at risk of hitting wraparound though (only 52% there).
Don.
--
Don Seiler
www.seiler.us
> by reducing "autovacuum_vacuum_cost_delay" to 2ms or less, and by
> increasing
> "maintenance_work_mem".
>
All autovacuum settings on this DB are default. Cost delay is at the
default 20ms. maintenance_work_mem I've already increased to 512MB (this VM
has 8GB RAM).
--
Don Seiler
www.seiler.us
bles
in our app DB triggering the autovacuum "to prevent wrap-around" when they
reach 200M. That's what had me concerned to see template0 with an age over
1B and no autovacuum even trying to clean up for it.
Don.
--
Don Seiler
www.seiler.us
progress, with over 850M dead tuples according to
pg_stat_all_tables. I estimate 3-4 more days to go on that one. Once that's
done I'll be scheduling manual vacuum jobs. Just wondering if that would
somehow affect regular template0 cleanup though.
I don't see anything in postgres log relat
On Tue, Apr 7, 2020 at 11:41 AM Don Seiler wrote:
>
> Follow-up question, the locale setting on the host would still be set to
> en_US (as would the postgres and template0 databases). Should I look to
> change that locale on the system to en_US.UTF-8, or even just for the
> pos
On Mon, Mar 30, 2020 at 4:39 PM Don Seiler wrote:
> On Mon, Mar 30, 2020 at 4:30 PM Tom Lane wrote:
>
>> Don Seiler writes:
>> > Actually, would I need to re-index on text columns that we know contain
>> > UUID strings? UUID characters seem to be pretty basic alph
On Mon, Mar 30, 2020 at 4:30 PM Tom Lane wrote:
> Don Seiler writes:
> > Actually, would I need to re-index on text columns that we know contain
> > UUID strings? UUID characters seem to be pretty basic alphanumeric ASCII
> > characters.
>
> I think you're all ri
On Sun, Mar 22, 2020 at 4:48 PM Don Seiler wrote:
>
> Here's the fun part. A lot of the tables use UUIDv4 strings for primary
> keys. However these are stored in text/varchar columns.
>
Actually, would I need to re-index on text columns that we know contain
UUID strings? UUID c
best course of action in my opinion if you have any
> doubts, because that's safe even if it has a higher cost.
>
Here's the fun part. A lot of the tables use UUIDv4 strings for primary
keys. However these are stored in text/varchar columns.
--
Don Seiler
www.seiler.us
after testing all of that heavily)?
What are the ramifications of changing collation like that? Should we
consider rebuilding indexes ASAP after that?
Don.
--
Don Seiler
www.seiler.us
On Tue, Mar 17, 2020 at 8:06 AM Don Seiler wrote:
> On Mon, Mar 16, 2020 at 10:28 AM Tom Lane wrote:
>
>>
> Well, in principle you could likewise manually update pg_database's
>> datcollate and datctype columns to say "en_US.utf8". However, there's
>
would logical replication safely replicate
and convert the data until we could then cut over?
Thanks,
Don.
--
Don Seiler
www.seiler.us
be sure.
Is logical replication an option here? Either maintaining the mixed
environment or converting everything to en_US.UTF-8? I'm relatively new in
this shop but I'm told they didn't mean to use en_US and there's no reason
they wouldn't want to just use the standard/default UTF-8.
Thanks,
Don.
--
Don Seiler
www.seiler.us
On Mon, Mar 2, 2020, 12:30 Vik Fearing wrote:
> On 02/03/2020 18:09, Don Seiler wrote:
> > The REAL reason for this is that I'm wondering if I created a NOT NULL
> > check constraint with "NOT VALID" would that then NOT be considered in
> such
> > a "
Planning time: 0.390 ms
Execution time: 2339.274 ms
(6 rows)
--
Don Seiler
www.seiler.us
optimizer/planner when
evaluating execution plans.
--
Don Seiler
www.seiler.us
provides a clean CLI for building
and using all the various supported versions:
https://github.com/theory/pgenv
Don.
--
Don Seiler
www.seiler.us
lication will only read from the WAL files in the
$PGDATA/pg_xlog directory. It will not read from archives. So, yes, you
would need your NFS mount on the replica (or otherwise copy the archive
files to the replica).
Don.
--
Don Seiler
www.seiler.us
n index on that
field.
Even as I experiment with some query rewrites, the EXPLAIN ANALYZE always
says rows=75. I'm *very* curious to see why it is using that value.
Don.
--
Don Seiler
www.seiler.us
ecords IS NOT NULL;
The foo.gifts table is pretty much the core table of our database. It's big
and very active. There is an index on date_added but not yet on
date_gifted.
I'm working to re-write the query while the dev sees if we even need this
query anymore.
On Wed, Aug 15, 2018 at 2:39
s that is far more selective and avoids throwing rows away. However I'm
very interested in why every node dealing with the gifts table thinks
rows=75 when the actual is much, much higher. And 75 seems like too round
of a number to be random?
--
Don Seiler
www.seiler.us
probably look to set it but make sure
to point out that it is just a safety net to let DB maintenance run and
they should make sure their work is committed cleanly if they want to keep
it.
Don.
--
Don Seiler
www.seiler.us
way. But if they're also NOT closing cursors, that seems like
another bad practice to correct.
Would commit/rollback automatically close cursors opened in that
transaction?
Don.
--
Don Seiler
www.seiler.us
On Mon, Aug 13, 2018 at 5:55 PM, Vick Khera wrote:
> On Mon, Aug 13, 2018 at 5:19 PM, Don Seiler wrote:
>
>>
>> I don't quite follow this. What circumstances would lead to this
>> situation?
>>
>
> BEGIN WORK;
> DECLARE CURSOR ... ;
> FETCH ...;
On Mon, Aug 13, 2018 at 4:04 PM, Don Seiler wrote:
>
>
> Anyway, my next step is getting the OK to terminate those idle in
> transaction sessions to see if that gets my vacuum job moving. Meanwhile
> I'll ask a dev to sort out why they might be sitting idle in transaction,
>
or what its worth, these sessions are backend reporting jobs, not user
interfacing at all.
Don.
--
Don Seiler
www.seiler.us
e I
could/should also look to get to the root cause of this.
Don.
--
Don Seiler
www.seiler.us
On Wed, Jun 13, 2018 at 11:16 AM, wrote:
>
>
> Can anyoen explain WHY there is such a big difference? Is it the SQL
> statement or a bug in the pgdump ?
>
>
Did you analyze the database after upgrading? Or at least the tables in
question? Those are very different plan
On Tue, Jun 12, 2018 at 10:48 AM, Don Seiler wrote:
> On Tue, Jun 12, 2018 at 2:26 AM, Haug Bürger
> wrote:
>
>>
>> "availability_cache_ac_session_id_fkey" FOREIGN KEY (ac_session_id)
>> REFERENCES zpg_data.session(id) ON DELETE CA
atically indexed and that can lead to horrible performance on
cascading operations like this.
--
Don Seiler
www.seiler.us
ld
> do this.
>
Are you using the PGDG repo packages, or the default CentOS repo packages?
You should use PGDG and those should install under /var/lib/pgsql.
Don.
--
Don Seiler
www.seiler.us
judgment on the site design until it is functioning.
Those links work fine for me on the new site. I haven't hit any issues yet
in my casual click-testing.
--
Don Seiler
www.seiler.us
On Wed, Apr 11, 2018 at 1:38 PM, Don Seiler wrote:
>
> Yeah, I saw the same with a 132 row insert. Now imagine that with a
> monthly 50 million row insert or delete. :p Thanks for the confirmation!
>
I went back to look at the postgres logs on my dev server. These logs are
rotated o
rameters: $1 = '3',
> $2 = 'three', $3 = 'f'
>
> So much for that idea(:=
Yeah, I saw the same with a 132 row insert. Now imagine that with a monthly
50 million row insert or delete. :p Thanks for the confirmation!
I'm definitely leaning towards the copy/load/delete method.
Don.
--
Don Seiler
www.seiler.us
On Wed, Apr 11, 2018 at 1:13 PM, Don Seiler wrote:
>
> I had considered this as well, as this would allow me to rollback the
> delete (assuming my intel on postgres_fdw transactions was correct, which
> it may not be after all). I wondered if a remote insert would be broken up
>
like the remote delete was, as that would be
equally unappealing for the same reasons. But obviously worth confirming.
Don.
--
Don Seiler
www.seiler.us
to hear what
others might be doing for tasks like this.
Don.
--
Don Seiler
www.seiler.us
than symlinking anyway. I'll look to do
that in my next round of config changes.
--
Don Seiler
www.seiler.us
They aren't needed for database
restore or recovery.
Don.
--
Don Seiler
www.seiler.us
t ready
to make the changes necessary in the production environment.
--
Don Seiler
www.seiler.us
ut for these
cases I just recover it to the first consistent point and open it for
testing (or backups in this case).
Thanks for all your help!
Don.
--
Don Seiler
www.seiler.us
ession as well but that
pg_basebackup does do.
I did come up with a sort of Rube Goldberg-esque workaround for now
involving using a clone of the prod standby VM from Veeam backup to use as
the backup source (after stopping recovery and opening it as a standalone
DB).
Don.
--
Don Seiler
www.seiler.us
On Mon, Feb 19, 2018 at 8:23 AM, David Steele wrote:
>
> Either is fine with me, but as Michael says I might miss postings to
> -general. I'm sure somebody else would catch it, though.
>
OK, I'll make use of the issues tracker going forward.
--
Don Seiler
www.seiler.us
hed my master/replica clone setup but I'll
test there as well. I just had a couple questions about the mechanics.
--
Don Seiler
www.seiler.us
see any mention of a forum
or list on their website, and there have been lots of pgBackRest questions
on this list in the past so I settled on this one.
Don.
--
Don Seiler
www.seiler.us
Does pgBackRest need to be installed and configured on the primary as
well?
Thanks,
Don.
--
Don Seiler
www.seiler.us
alyze-only
I wonder if it wouldn't be a bad idea to also mention the --jobs=N
parameter option in that blurb. Yes it's in the --help text but it wouldn't
be bad to highlight its availability.
Don.
--
Don Seiler
www.seiler.us
I don't see any errors in /var/log/messages that would incidate any
filesystem issues either. Obviously the worry is for possible corruption.
Granted this is "only" pre-prod but there will be concerns from the
business side prior to upgrading our prod DB (currently 9.2.22).
What else can/should I check here to make sure there isn't something wrong
with this database cluster?
--
Don Seiler
www.seiler.us
63 matches
Mail list logo
