pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcloudNativePg bootstrap from dump
6+ messages / 3 participants
[nested] [flat]
* cloudNativePg bootstrap from dump
@ 2024-05-10 14:02 Alessandro Dentella <sandro.dentella@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Alessandro Dentella @ 2024-05-10 14:02 UTC (permalink / raw)
To: pgsql-admin
Hi
[I'm not sure if cloudNativePg questions are suitable for this list, let me
know if not...]
I'd like to bootstrap a Cluster in CNPG from a PostgreSQL dump but I'm not
sure it can be done and in case how.
Docs show 3 options: 1. initdb,2. pg_basebackup and 3. recovery
Initdb seems the correct one to me and I could just start an empty db and
psql data into it.
When I do that data seem to to get in but the psql process never ends:
kubectl cnpg psql my-db < dump.sql
As a second option I'd like to know how to bake a backup from a Postgres
instance suitable for the 'recovery' option.
If I understand this options would require me to stick to the same
PostgreSQL version/
If I backup a db (kubectl cnpg backup) I get a directory with data.tar.gz
(the physical backup) and a JSON file that describes it.
I guess the data are produced by pg_basebackup, bu how should I produce the
json description?
sandro
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: cloudNativePg bootstrap from dump
@ 2024-05-10 15:48 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Alessandro Dentella <sandro.dentella@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Scott Ribe @ 2024-05-10 15:48 UTC (permalink / raw)
To: Alessandro Dentella <sandro.dentella@gmail.com>; +Cc: pgsql-admin
Is it possible that it is not stuck, but simply processing rows of a large table? Try with the -e option, then you'll see.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: cloudNativePg bootstrap from dump
@ 2024-05-10 16:15 Alessandro Dentella <sandro.dentella@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 2 replies; 6+ messages in thread
From: Alessandro Dentella @ 2024-05-10 16:15 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin
Il giorno ven 10 mag 2024 alle ore 17:49 Scott Ribe <
scott_ribe@elevated-dev.com> ha scritto:
> Is it possible that it is not stuck, but simply processing rows of a large
> table? Try with the -e option, then you'll see.
I'd say no. based on the fact tat If I query the dimension with \l+ I see
85 MB, that is exactly the same dim I find in a db initialized with the
same dump. where the import finishes correctly (not in k8s).
On the other side If I add -e and look at the issued SQL statement, many
create table statement are missing (but the table are there).
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: cloudNativePg bootstrap from dump
@ 2024-05-10 16:19 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Alessandro Dentella <sandro.dentella@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Scott Ribe @ 2024-05-10 16:19 UTC (permalink / raw)
To: Alessandro Dentella <sandro.dentella@gmail.com>; +Cc: pgsql-admin
> On May 10, 2024, at 10:15 AM, Alessandro Dentella <sandro.dentella@gmail.com> wrote:
>
> I'd say no. based on the fact tat If I query the dimension with \l+ I see 85 MB, that is exactly the same dim I find in a db initialized with the same dump. where the import finishes correctly (not in k8s).
>
> On the other side If I add -e and look at the issued SQL statement, many create table statement are missing (but the table are there).
I think -e only gives you DML, not DDL. For that you need --echo-all
Maybe it's just not closing the connection as you expect when it hits end of file? Maybe something about how that cnf command works???
Sitting there waiting for another command?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: cloudNativePg bootstrap from dump
@ 2024-05-10 17:45 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Alessandro Dentella <sandro.dentella@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Ron Johnson @ 2024-05-10 17:45 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, May 10, 2024 at 12:16 PM Alessandro Dentella <
sandro.dentella@gmail.com> wrote:
>
>
> Il giorno ven 10 mag 2024 alle ore 17:49 Scott Ribe <
> scott_ribe@elevated-dev.com> ha scritto:
>
>> Is it possible that it is not stuck, but simply processing rows of a
>> large table? Try with the -e option, then you'll see.
>
>
> I'd say no. based on the fact tat If I query the dimension with \l+ I see
> 85 MB, that is exactly the same dim I find in a db initialized with the
> same dump. where the import finishes correctly (not in k8s).
>
> On the other side If I add -e and look at the issued SQL statement, many
> create table statement are missing (but the table are there).
>
iotop and pg_stat_activity will tell you what is (or is not) happening.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: cloudNativePg bootstrap from dump
@ 2024-05-11 11:31 Alessandro Dentella <sandro.dentella@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Alessandro Dentella @ 2024-05-11 11:31 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Thanks for pointing out that, but alas no news.
pg_stat_activity shows exactly the same rows as before the import, I also
looked at pg_locks, that show nothing new.
Iotop doesn't show anything relevant to me.
Here is the output from postgres, but I think it doesn't say anything
interesting
postgres=# select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid |
transactionid | classid | objid | objsubid | virtualtransaction | pid |
mode | granted | fastpath | waitstart
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+-----------------+---------+----------+-----------
relation | 5 | 12073 | | | |
| | | | 3/155 | 104 | AccessShareLock
| t | t |
virtualxid | | | | | 3/155 |
| | | | 3/155 | 104 | ExclusiveLock
| t | t |
(2 rows)
postgres=# select * from pg_stat_activity ;
datid | datname | pid | leader_pid | usesysid | usename |
application_name | client_addr | client_hostname | client_port |
backend_start | xact_start |
query_start | state_change | wait_event_type |
wait_event | state | backend_xid | backend_xmin | query_id |
query | backend_type
-------+----------+-----+------------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+------------------------------+-----------------+---------------------+--------+-------------+--------------+----------+----------------------------------+------------------------------
| | 26 | | | |
| | | | 2024-05-11
11:14:05.407952+00 | |
| | Activity | AutoVacuumMain
| | | | |
| autovacuum launcher
| | 28 | | 10 | postgres |
| | | | 2024-05-11
11:14:05.408858+00 | |
| | Activity |
LogicalLauncherMain | | | | |
| logical replication launcher
5 | postgres | 104 | | 10 | postgres | psql
| | | -1 | 2024-05-11
11:14:07.77121+00 | 2024-05-11 11:17:19.589227+00 | 2024-05-11
11:17:19.589227+00 | 2024-05-11 11:17:19.58923+00 | |
| active | | 1192 | | select *
from pg_stat_activity ; | client backend
| | 23 | | | |
| | | | 2024-05-11
11:14:05.395614+00 | |
| | Activity |
BgWriterHibernate | | | | |
| background writer
| | 27 | | | |
| | | | 2024-05-11
11:14:05.408225+00 | |
| | Activity | ArchiverMain
| | | | |
| archiver
| | 22 | | | |
| | | | 2024-05-11
11:14:05.39521+00 | |
| | Activity | CheckpointerMain
| | | | |
| checkpointer
| | 25 | | | |
| | | | 2024-05-11
11:14:05.407708+00 | |
| | Activity | WalWriterMain
| | | | |
| walwriter
(7 rows)
Il giorno ven 10 mag 2024 alle ore 19:45 Ron Johnson <
ronljohnsonjr@gmail.com> ha scritto:
> On Fri, May 10, 2024 at 12:16 PM Alessandro Dentella <
> sandro.dentella@gmail.com> wrote:
>
>>
>>
>> Il giorno ven 10 mag 2024 alle ore 17:49 Scott Ribe <
>> scott_ribe@elevated-dev.com> ha scritto:
>>
>>> Is it possible that it is not stuck, but simply processing rows of a
>>> large table? Try with the -e option, then you'll see.
>>
>>
>> I'd say no. based on the fact tat If I query the dimension with \l+ I see
>> 85 MB, that is exactly the same dim I find in a db initialized with the
>> same dump. where the import finishes correctly (not in k8s).
>>
>> On the other side If I add -e and look at the issued SQL statement, many
>> create table statement are missing (but the table are there).
>>
>
> iotop and pg_stat_activity will tell you what is (or is not) happening.
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-05-11 11:31 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-10 14:02 cloudNativePg bootstrap from dump Alessandro Dentella <sandro.dentella@gmail.com>
2024-05-10 15:48 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-05-10 16:15 ` Alessandro Dentella <sandro.dentella@gmail.com>
2024-05-10 16:19 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-05-10 17:45 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-05-11 11:31 ` Alessandro Dentella <sandro.dentella@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox