On Sunday, January 15, 2023, jian he wrote:
>
>
> In the following example, I cannot see bloat (via extension pgstattuple
> dead_tuple_count>0). Wondering where the bloat is.
>
> do the update.
> update tbt set a = 10 + a where a < 20;
> REFRESH MATERIALIZED view tbtmv;
> SELECT * FROM p
On Mon, Jan 16, 2023 at 10:28 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Sunday, January 15, 2023, jian he wrote:
>
>>
>>
>> Hi,
>> why the materialized view itself bloats. If no refresh then no bloat
>> right? If fresh then set based delete operation will make materialized vi
On Sunday, January 15, 2023, jian he wrote:
>
>
> Hi,
> why the materialized view itself bloats. If no refresh then no bloat
> right? If fresh then set based delete operation will make materialized view
> bloat?
> I also found the same question online. https://dba.stackexchange.com/
> questions/2
On Sat, Jan 14, 2023 at 11:49 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Friday, January 13, 2023, jian he wrote:
>
>>
>> Once we have the diff table, we perform set-based DELETE and INSERT
>>> operations against the materialized view, and discard both temporary
>>> tables.
>>
On Mon, 16 Jan 2023 09:16:27 +1100 Gavan Schneider wrote:
>On 16 Jan 2023, at 8:59, p...@pfortin.com wrote:
>
>> encodings for database "template1" do not match: old "UTF8", new
>> "SQL_ASCII" Failure, exiting
>>
>Suggest the old dB using UTF8 is the better practice, and the new dB should do
>l
p...@pfortin.com writes:
> encodings for database "template1" do not match: old "UTF8", new
> "SQL_ASCII" Failure, exiting
So you need to do the initdb under the same locale setting you
used for the old DB. Looking into its LC_XXX settings should
refresh your memory on what that was.
We regularly use "history" databases. Put them on slow media, and only take
a backup when data is added to them (monthly, quarterly, etc).
On 1/15/23 15:57, HECTOR INGERTO wrote:
> But you cannot and should not rely on snapshots alone
That’s only for non atomic (multiple pools) snapshots. Is
On 16 Jan 2023, at 8:59, p...@pfortin.com wrote:
> encodings for database "template1" do not match: old "UTF8", new
> "SQL_ASCII" Failure, exiting
>
Suggest the old dB using UTF8 is the better practice, and the new dB should do
likewise
> "template1" is not a DB I've ever messed with; so this wi
On Sun, 2023-01-15 at 16:59 -0500, p...@pfortin.com wrote:
>
>
> encodings for database "template1" do not match: old "UTF8", new
> "SQL_ASCII" Failure, exiting
You almost certainly don't want your new database to use SQL_ASCII.
Init the new cluster with -E UTF8.
> But you cannot and should not rely on snapshots alone
That’s only for non atomic (multiple pools) snapshots. Isn’t?
If I need to rely only on ZFS (automated) snapshots, then the best option would
be to have two DB? Each one in each own pool. One HDD DB and one SSD DB. Then,
the backend code
On Sun, Jan 15, 2023 at 10:57 PM HECTOR INGERTO
wrote:
>
>
> > But you cannot and should not rely on snapshots alone
>
>
>
> That’s only for non atomic (multiple pools) snapshots. Isn’t?
>
Right. For single-filesystem installs it should be fine. Just make sure it
has both the data and the WAL di
On Sun, 15 Jan 2023 16:38:08 -0500 p...@pfortin.com wrote:
>On Sun, 15 Jan 2023 15:59:20 -0500 Tom Lane wrote:
>
>>p...@pfortin.com writes:
>>> On Sun, 15 Jan 2023 14:47:35 -0500 Tom Lane wrote:
I think you misunderstand how this is supposed to work. The -D
argument should point a
On Sun, 15 Jan 2023 15:59:20 -0500 Tom Lane wrote:
>p...@pfortin.com writes:
>> On Sun, 15 Jan 2023 14:47:35 -0500 Tom Lane wrote:
>>> I think you misunderstand how this is supposed to work. The -D
>>> argument should point at an *empty* data directory that has been
>>> freshly initialized with
On Sun, 15 Jan 2023 13:00:58 -0800 Adrian Klaver wrote:
>On 1/15/23 12:41, p...@pfortin.com wrote:
>> On Sun, 15 Jan 2023 12:23:10 -0800 Adrian Klaver wrote:
>>
>>> On 1/15/23 11:27, p...@pfortin.com wrote:
Hi,
I'm fairly new to postgres; but have databases with about 2TB of da
Adrian Klaver writes:
> --clone
I think --clone is probably contraindicated here, given that Pierre
already made a copy of the data. If I understand how that works,
it'll just wind up making another whole copy, but in a time-extended
manner as the tables are modified. Over the long run there wo
On 1/15/23 12:41, p...@pfortin.com wrote:
On Sun, 15 Jan 2023 12:23:10 -0800 Adrian Klaver wrote:
On 1/15/23 11:27, p...@pfortin.com wrote:
Hi,
I'm fairly new to postgres; but have databases with about 2TB of data.
Trying to upgrade from 13.6 to 15.1, pg_upgrade complains with:
[postgres@pf
p...@pfortin.com writes:
> On Sun, 15 Jan 2023 14:47:35 -0500 Tom Lane wrote:
>> I think you misunderstand how this is supposed to work. The -D
>> argument should point at an *empty* data directory that has been
>> freshly initialized with the new version's initdb. pg_upgrade then
>> transfers da
On Sun, 15 Jan 2023 12:23:10 -0800 Adrian Klaver wrote:
>On 1/15/23 11:27, p...@pfortin.com wrote:
>> Hi,
>>
>> I'm fairly new to postgres; but have databases with about 2TB of data.
>>
>> Trying to upgrade from 13.6 to 15.1, pg_upgrade complains with:
>> [postgres@pf ~]$ /usr/bin/pg_upgrade -b
On Sun, 15 Jan 2023 14:47:35 -0500 Tom Lane wrote:
>p...@pfortin.com writes:
>> Trying to upgrade from 13.6 to 15.1, pg_upgrade complains with:
>> [postgres@pf ~]$ /usr/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/bin \
>> -d /mnt/db/var/lib/pgsql/data -D /mnt/work/var/lib/pgsql/data \
>> -s
On 1/15/23 11:27, p...@pfortin.com wrote:
Hi,
I'm fairly new to postgres; but have databases with about 2TB of data.
Trying to upgrade from 13.6 to 15.1, pg_upgrade complains with:
[postgres@pf ~]$ /usr/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/bin \
-d /mnt/db/var/lib/pgsql/data -D /mn
p...@pfortin.com writes:
> Trying to upgrade from 13.6 to 15.1, pg_upgrade complains with:
> [postgres@pf ~]$ /usr/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/bin \
> -d /mnt/db/var/lib/pgsql/data -D /mnt/work/var/lib/pgsql/data \
> -s /tmp -U postgres
> This utility can only upgrade to Pos
On Sun, Jan 15, 2023 at 8:18 PM HECTOR INGERTO
wrote:
> Hello everybody,
>
>
>
> I’m using PostgreSQL on openZFS. I use ZFS snapshots as a backup +
> hotspare method.
>
>
>
> From man zfs-snapshot: “Snapshots are taken atomically, so that all
> snapshots correspond to the same moment in time.” So
Hi,
I'm fairly new to postgres; but have databases with about 2TB of data.
Trying to upgrade from 13.6 to 15.1, pg_upgrade complains with:
[postgres@pf ~]$ /usr/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/bin \
-d /mnt/db/var/lib/pgsql/data -D /mnt/work/var/lib/pgsql/data \
-s /tmp -U po
Hello everybody,
I’m using PostgreSQL on openZFS. I use ZFS snapshots as a backup + hotspare
method.
>From man zfs-snapshot: “Snapshots are taken atomically, so that all snapshots
>correspond to the same moment in time.” So if a PSQL instance is started from
>a zfs snapshot, it will start to r
24 matches
Mail list logo
