Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

pg fails to import data on initial docker compose #5

Closed
pulseone-cp opened this issue Oct 27, 2022 · 4 comments
Closed

pg fails to import data on initial docker compose #5

pulseone-cp opened this issue Oct 27, 2022 · 4 comments

Comments

@pulseone-cp
Copy link

running clean clone using docker compose on debian 11 fails to import data

[+] Running 3/0
 ⠿ Container postgres-sb-mirror   Created                                                                                                    0.0s
 ⠿ Container sb-mirror            Created                                                                                                    0.0s
 ⠿ Container sponsorblock-mirror  Created                                                                                                    0.0s
Attaching to postgres-sb-mirror, sb-mirror, sponsorblock-mirror
sb-mirror            | Uses SponsorBlock data from https://sponsor.ajay.app/
postgres-sb-mirror   |
postgres-sb-mirror   | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres-sb-mirror   |
sb-mirror            | Downloading from mirror: sponsorblock.kavin.rocks
postgres-sb-mirror   | 2022-10-27 16:31:33.147 UTC [1] LOG:  starting PostgreSQL 15.0 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit
postgres-sb-mirror   | 2022-10-27 16:31:33.147 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres-sb-mirror   | 2022-10-27 16:31:33.147 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres-sb-mirror   | 2022-10-27 16:31:33.162 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres-sb-mirror   | 2022-10-27 16:31:33.173 UTC [24] LOG:  database system was shut down at 2022-10-27 16:30:07 UTC
postgres-sb-mirror   | 2022-10-27 16:31:33.180 UTC [1] LOG:  database system is ready to accept connections
sponsorblock-mirror  | Importing database...
postgres-sb-mirror   | 2022-10-27 16:31:34.040 UTC [35] ERROR:  missing data for column "startTime"
postgres-sb-mirror   | 2022-10-27 16:31:34.040 UTC [35] CONTEXT:  COPY sponsorTimesTemp, line 2: "/*!"
postgres-sb-mirror   | 2022-10-27 16:31:34.040 UTC [35] STATEMENT:  BEGIN; DROP TABLE IF EXISTS "sponsorTimesTemp"; CREATE UNLOGGED TABLE "sponsorTimesTemp"(LIKE "sponsorTimes" INCLUDING defaults INCLUDING constraints INCLUDING indexes); COPY "sponsorTimesTemp" FROM '/mirror/sponsorTimes.csv' DELIMITER ',' CSV HEADER; DROP TABLE "sponsorTimes"; ALTER TABLE "sponsorTimesTemp" RENAME TO "sponsorTimes"; COMMIT;
sponsorblock-mirror  | Imported database in 55ms
sponsorblock-mirror  | Failed to import database: missing data for column "startTime"
postgres-sb-mirror   | 2022-10-27 16:31:34.043 UTC [35] ERROR:  current transaction is aborted, commands ignored until end of transaction block
postgres-sb-mirror   | 2022-10-27 16:31:34.043 UTC [35] STATEMENT:  VACUUM "sponsorTimes";
sponsorblock-mirror  | Failed to vacuum database: current transaction is aborted, commands ignored until end of transaction block
sb-mirror            | receiving incremental file list
sb-mirror            | ./
sb-mirror            | mirror.sb.mchang.xyz/
sb-mirror            | mirror.sb.mchang.xyz/categoryVotes.csv
     24,827,717 100%  124.62MB/s    0:00:00 (xfr#1, to-chk=17/29)
sb-mirror            | mirror.sb.mchang.xyz/index.html
         17,694 100%   90.94kB/s    0:00:00 (xfr#2, to-chk=16/29)
sb-mirror            | mirror.sb.mchang.xyz/index.html?sort=name&order=asc
         17,967 100%   92.35kB/s    0:00:00 (xfr#3, to-chk=15/29)
sb-mirror            | mirror.sb.mchang.xyz/index.html?sort=name&order=desc
         17,968 100%   92.35kB/s    0:00:00 (xfr#4, to-chk=14/29)
sb-mirror            | mirror.sb.mchang.xyz/index.html?sort=namedirfirst&order=asc
         17,694 100%   90.47kB/s    0:00:00 (xfr#5, to-chk=13/29)
sb-mirror            | mirror.sb.mchang.xyz/index.html?sort=namedirfirst&order=desc
         17,695 100%   90.47kB/s    0:00:00 (xfr#6, to-chk=12/29)
sb-mirror            | mirror.sb.mchang.xyz/index.html?sort=size&order=asc
         17,967 100%   91.38kB/s    0:00:00 (xfr#7, to-chk=11/29)
sb-mirror            | mirror.sb.mchang.xyz/index.html?sort=size&order=desc
         17,968 100%   91.39kB/s    0:00:00 (xfr#8, to-chk=10/29)
sb-mirror            | mirror.sb.mchang.xyz/index.html?sort=time&order=asc
         17,967 100%   91.38kB/s    0:00:00 (xfr#9, to-chk=9/29)
sb-mirror            | mirror.sb.mchang.xyz/index.html?sort=time&order=desc
         17,968 100%   91.39kB/s    0:00:00 (xfr#10, to-chk=8/29)
sb-mirror            | mirror.sb.mchang.xyz/lockCategories.csv
     15,347,300 100%   54.82MB/s    0:00:00 (xfr#11, to-chk=7/29)
sb-mirror            | mirror.sb.mchang.xyz/ratings.csv
        924,650 100%    3.19MB/s    0:00:00 (xfr#12, to-chk=6/29)
sb-mirror            | mirror.sb.mchang.xyz/sponsorTimes.csv
  3,797,080,277 100%  135.07MB/s    0:00:26 (xfr#13, to-chk=5/29)
sb-mirror            | mirror.sb.mchang.xyz/unlistedVideos.csv
      5,729,807 100%    6.23MB/s    0:00:00 (xfr#14, to-chk=4/29)
sb-mirror            | mirror.sb.mchang.xyz/userNames.csv
     13,941,428 100%   13.32MB/s    0:00:00 (xfr#15, to-chk=3/29)
sb-mirror            | mirror.sb.mchang.xyz/videoInfo.csv
    401,983,079 100%   89.01MB/s    0:00:04 (xfr#16, to-chk=2/29)
sb-mirror            | mirror.sb.mchang.xyz/vipUsers.csv
          6,117 100%   19.21kB/s    0:00:00 (xfr#17, to-chk=1/29)
sb-mirror            | mirror.sb.mchang.xyz/warnings.csv
        454,524 100%    1.38MB/s    0:00:00 (xfr#18, to-chk=0/29)
sb-mirror            |
sb-mirror            | sent 388 bytes  received 2,554,704,353 bytes  47,751,490.49 bytes/sec
sb-mirror            | total size is 4,260,906,327  speedup is 1.67
sb-mirror            | Starting rsync daemon
sb-mirror            | Downloading from mirror: sponsorblock.kavin.rocks
sb-mirror            | Downloading from mirror: sponsorblock.kavin.rocks
sb-mirror            | receiving incremental file list
sb-mirror            | receiving incremental file list
sb-mirror            | categoryVotes.csv
         50,060 100%   47.74MB/s    0:00:00 (xfr#1, to-chk=27/29)
sb-mirror            | ./
sb-mirror            | categoryVotes.csv
         50,060 100%   47.74MB/s    0:00:00 (xfr#1, to-chk=27/29)
sb-mirror            | lockCategories.csv
sb-mirror            | lockCategories.csv
         50,060 100%   47.74MB/s    0:00:00 (xfr#2, to-chk=26/29)
sb-mirror            | ratings.csv
         50,060 100%   47.74MB/s    0:00:00 (xfr#3, to-chk=25/29)
sb-mirror            | sponsorTimes.csv
         50,060 100%   47.74MB/s    0:00:00 (xfr#4, to-chk=24/29)
sb-mirror            | unlistedVideos.csv
         50,060 100%   23.87MB/s    0:00:00 (xfr#2, to-chk=26/29)
         50,060 100%   47.74MB/s    0:00:00 (xfr#5, to-chk=23/29)
sb-mirror            | userNames.csv
         50,060 100%   47.74MB/s    0:00:00 (xfr#6, to-chk=22/29)
sb-mirror            | ratings.csv
sb-mirror            | videoInfo.csv
         50,060 100%   47.74MB/s    0:00:00 (xfr#7, to-chk=21/29)
sb-mirror            | vipUsers.csv
         50,060 100%   23.87MB/s    0:00:00 (xfr#8, to-chk=20/29)
sb-mirror            | warnings.csv
         50,060 100%   15.91MB/s    0:00:00 (xfr#3, to-chk=25/29)
         50,060 100%   23.87MB/s    0:00:00 (xfr#9, to-chk=19/29)
sb-mirror            | sponsorTimes.csv
         50,060 100%   15.91MB/s    0:00:00 (xfr#4, to-chk=24/29)
sb-mirror            | unlistedVideos.csv
         50,060 100%   11.94MB/s    0:00:00 (xfr#5, to-chk=23/29)
sb-mirror            | userNames.csv
         50,060 100%   11.94MB/s    0:00:00 (xfr#6, to-chk=22/29)
sb-mirror            | videoInfo.csv
         50,060 100%   11.94MB/s    0:00:00 (xfr#7, to-chk=21/29)
sb-mirror            | vipUsers.csv
         50,060 100%    9.55MB/s    0:00:00 (xfr#8, to-chk=20/29)
sb-mirror            | warnings.csv
         50,060 100%    9.55MB/s    0:00:00 (xfr#9, to-chk=19/29)
sponsorblock-mirror  | Importing database...
postgres-sb-mirror   | 2022-10-27 16:35:34.046 UTC [35] ERROR:  current transaction is aborted, commands ignored until end of transaction block
postgres-sb-mirror   | 2022-10-27 16:35:34.046 UTC [35] STATEMENT:  BEGIN; DROP TABLE IF EXISTS "sponsorTimesTemp"; CREATE UNLOGGED TABLE "sponsorTimesTemp"(LIKE "sponsorTimes" INCLUDING defaults INCLUDING constraints INCLUDING indexes); COPY "sponsorTimesTemp" FROM '/mirror/sponsorTimes.csv' DELIMITER ',' CSV HEADER; DROP TABLE "sponsorTimes"; ALTER TABLE "sponsorTimesTemp" RENAME TO "sponsorTimes"; COMMIT;
postgres-sb-mirror   | 2022-10-27 16:35:34.047 UTC [35] ERROR:  current transaction is aborted, commands ignored until end of transaction block
postgres-sb-mirror   | 2022-10-27 16:35:34.047 UTC [35] STATEMENT:  VACUUM "sponsorTimes";
sponsorblock-mirror  | Imported database in 0ms
sponsorblock-mirror  | Failed to import database: current transaction is aborted, commands ignored until end of transaction block
sponsorblock-mirror  | Failed to vacuum database: current transaction is aborted, commands ignored until end of transaction block
sb-mirror            |
sb-mirror            |
sb-mirror            | sent 4,095 bytes  received 12,867 bytes  389.93 bytes/sec
sb-mirror            | total size is 4,260,906,327  speedup is 251,203.06
sb-mirror            | sent 4,098 bytes  received 12,870 bytes  390.07 bytes/sec
sb-mirror            | total size is 4,260,906,327  speedup is 251,114.23
postgres-sb-mirror   | 2022-10-27 16:36:33.272 UTC [22] LOG:  checkpoint starting: time
postgres-sb-mirror   | 2022-10-27 16:36:36.726 UTC [22] LOG:  checkpoint complete: wrote 37 buffers (0.2%); 0 WAL file(s) added, 0 removed, 0 recycled; write=3.423 s, sync=0.009 s, total=3.455 s; sync files=28, longest=0.005 s, average=0.001 s; distance=164 kB, estimate=164 kB
@pulseone-cp
Copy link
Author

quckfix: seems like mountpoints for postgres and sponsorblokc-mirror are off in compose file (as mirror downloads to seperate directory)

i was able to work around this by

1.)
changing volume mountpoint for postgres to
- ./mirror/mirror.sb.mchang.xyz:/mirror
and changing volume mountpoint of sponsorblock-mirror to
- ./mirror/mirror.sb.mchang.xyz:/app/mirror

2.)
modifying chmod (i know it's bad, it's a quick fix tho)
chmos 777 ./mirror/mirror.sb.mchang.xyz/*

@FireMasterK
Copy link
Member

changing volume mountpoint for postgres to

  • ./mirror/mirror.sb.mchang.xyz:/mirror
    and changing volume mountpoint of sponsorblock-mirror to
  • ./mirror/mirror.sb.mchang.xyz:/app/mirror

There was an error on my part, and this should be reverted.

modifying chmod (i know it's bad, it's a quick fix tho)

Yep, this is currently necessary for Postgres to read the file.

@pulseone-cp
Copy link
Author

pulseone-cp commented Oct 27, 2022 •
edited

can confirm your rsync mirror is now proper

feel free to add sponsorblock.gleesh.net (Location DE, rsync and https) to list of mirrors

@FireMasterK
Copy link
Member

Closing this with 6608a99, which makes it a simple docker-compose up -d for new installations, no more permission errors.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@FireMasterK @pulseone-cp