Defacto2, database

The Defacto2 database collects thousands of records documenting the history of the PC Scene and is the data powers the famed Defacto2 website.

There are daily exports of the SQL database.

DOWNLOAD: defacto2.net/sql/files.sql.

PostgreSQL 16+ is recommended for compatibility.

Tip

There is a RESTful API available.
defacto2.net/api.

First time setup

These instructions are for a Linux or macOS environment with a running PostgreSQL application and assumes the postgres username.

1. Download the SQL export file
2. Create a new database in your PostgreSQL server
3. Import the SQL export file into the new database
4. Test the table creation and data

cd ~

# 1) download the latest SQL file
curl https://defacto2.net/sql/files.sql --output files.sql

# 2) create the database
createdb --username=postgres defacto2_ps

# 3) import the table and the data
psql --username=postgres --dbname=defacto2_ps --file=files.sql

To test the table creation and data.

psql --username=postgres --dbname='defacto2_ps' --command='SELECT COUNT(*) FROM files;'

 count 
-------
  50000
(1 row)

Tip

The count result will depend on the latest export which will not be 50000 exactly.

Update records with the latest data

Download and import the latest SQL file.

cd ~
curl https://defacto2.net/sql/files.sql --output files.sql
psql --username=postgres --dbname=defacto2_ps --file=files.sql

Update a Docker container with the latest data

These instructions are for a Linux or macOS environment with a running a Docker PostgreSQL container with the following assumptions:

• Container name: pgdb
• Database name: defacto2_ps
• Database username: postgres

cd ~

# download the SQL file
curl https://defacto2.net/sql/files.sql --output files.sql

# copy the SQL file into the container
docker cp files.sql pgdb:/tmp/files.sql

# restore the data
docker exec pgdb psql --username=postgres --dbname=defacto2_ps --file=/tmp/files.sql

# cleanup
docker exec pgdb rm /tmp/files.sql

To test the data import.

docker exec -it pgdb psql --username=postgres --dbname='defacto2_ps' --command='SELECT COUNT(*) FROM files;'

 count
-------
  50000
(1 row)