Data Dumps

Last updated

To meet the requests of archivers, statisticians, and data analysts alike, Derpibooru provides data dumps on a nightly basis. These data consist only of publicly accessible database contents. These dumps are provided in pg_dump's custom format, and are created using PostgreSQL 12.2.

To load an entire dump (requires 29 GB of free storage):

    dropdb --if-exists derpibooru
    createdb derpibooru
    pg_restore -O -d derpibooru derpibooru_public_dump_2020_07_06.pgdump
  

This will create the database derpibooru with the following table layout:

public.badge_awards

Name Type Nullable?
id bigint
user_id bigint
badge_id bigint
created_at timestamp
label text

public.badges

Name Type Nullable?
id bigint
title text
description text
image_url text

public.comments

Name Type Nullable?
id bigint
image_id bigint
created_at timestamp
updated_at timestamp
user_id bigint
body text

public.duplicate_reports

Name Type Nullable?
id bigint
image_id bigint
duplicate_of_image_id bigint
created_at timestamp
updated_at timestamp
user_id bigint
state text
reason text

public.forums

Name Type Nullable?
id bigint
topic_count bigint
post_count bigint
created_at timestamp
updated_at timestamp
name text
short_name text
description text

public.galleries

Name Type Nullable?
id bigint
thumbnail_id bigint
user_id bigint
image_count bigint
created_at timestamp
updated_at timestamp
title text
spoiler_warning text
description text

public.gallery_interactions

Name Type Nullable?
image_id bigint
gallery_id bigint
position integer

public.image_duplicates

Name Type Nullable?
image_id bigint
target_id bigint

public.image_faves

Name Type Nullable?
image_id bigint
user_id bigint
created_at timestamp

public.image_features

Name Type Nullable?
image_id bigint
created_at timestamp

public.image_intensities

Name Type Nullable?
image_id bigint
nw_intensity double precision
ne_intensity double precision
sw_intensity double precision
se_intensity double precision
avg_intensity double precision

public.image_sources

Name Type Nullable?
image_id bigint
source text

public.image_taggings

Name Type Nullable?
image_id bigint
tag_id bigint

public.images

Name Type Nullable?
id bigint
created_at timestamp
updated_at timestamp
image_width integer
image_height integer
image_size integer
comment_count integer
score integer
favorites integer
upvotes integer
downvotes integer
hides integer
image_aspect_ratio double precision
user_id bigint
image_mime_type text
image_format text
image_name text
version_path text
image_sha512_hash text
image_orig_sha512_hash text

public.posts

Name Type Nullable?
id bigint
topic_id bigint
created_at timestamp
updated_at timestamp
user_id bigint
body text

public.source_changes

Name Type Nullable?
id bigint
image_id bigint
created_at timestamp
updated_at timestamp
user_id bigint
new_value text

public.tag_aliases

Name Type Nullable?
tag_id bigint
target_tag_id bigint

public.tag_changes

Name Type Nullable?
id bigint
image_id bigint
created_at timestamp
updated_at timestamp
user_id bigint
tag_id bigint
added boolean
tag_name_cache text

public.tag_implications

Name Type Nullable?
tag_id bigint
target_tag_id bigint

public.tags

Name Type Nullable?
id bigint
image_count bigint
name text
slug text
category text
description text
short_description text

public.topics

Name Type Nullable?
id bigint
forum_id bigint
post_count bigint
view_count bigint
created_at timestamp
updated_at timestamp
sticky boolean
locked boolean
title text
slug text

public.user_links

Name Type Nullable?
id bigint
user_id bigint
tag_id bigint
url text

public.users

Name Type Nullable?
id bigint
name text

Finer granularity over the imported data can be controlled with pg_restore flags; see the documentation for more detail.

Download

Size Link SHA-256
3.0G derpibooru_public_dump_2020_07_06.pgdump 400c5d38715fe5b32c4658d9ec8ff93e7fc07b1ba9cc904a2a75d01886ef2f45

Note: these dumps do not include images.

Revision history