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
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
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
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 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
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 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
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 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
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 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
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 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, 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
> 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, 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.
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
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
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.
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
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 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 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:
>
>
> 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
24 matches
Mail list logo
