Happy Birthday, MLP:FiM! MLP:FiM turns 10 years old this year! Let's celebrate with an art event!

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.4.

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

    dropdb --if-exists derpibooru
    createdb derpibooru
    pg_restore -O -d derpibooru derpibooru_public_dump_2020_10_20.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.9G derpibooru_public_dump_2020_10_20.pgdump 30d49bdbd9e14055588e2bc08427973e4becb438df816e1adcd1373cf5d65575

Note: these dumps do not include images.

Revision history