Data Dumps

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 latest PostgreSQL 15.x.

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

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

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

public.artist_links

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

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.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
4.6G	derpibooru_public_dump_2024_04_19.pgdump	3e05ae8c9850328660e53046fc9cf28c55b1a0e89e1961a6bd32eb4dee13386e

Note: these dumps do not include images.