Last active
October 24, 2025 18:58
-
-
Save viccherubini/25a12ba466b8ab1371f580d1398dea16 to your computer and use it in GitHub Desktop.
PostgreSQL database dump script
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/usr/bin/env php | |
| <?php | |
| error_reporting(0); | |
| /** | |
| * Data from the tables below will not be included. | |
| * | |
| * @var list<non-empty-string> | |
| */ | |
| $excludeTableDataTables = [ | |
| 'event', | |
| 'sessions', | |
| ]; | |
| if ('1' === getenv('DB_DUMP_LITE')) { | |
| // $excludeTableDataTables[] = 'table_a'; | |
| // $excludeTableDataTables[] = 'table_b'; | |
| } | |
| $pgPassPath = sprintf('%s/.pgpass', (string) getenv('HOME')); | |
| if (!is_file($pgPassPath)) { | |
| printf("Missing .pgpass file in '%s', aborting.\n", $pgPassPath); | |
| exit(1); | |
| } | |
| $pgPassBits = explode(':', trim((string) file_get_contents($pgPassPath)), 5); | |
| if (5 !== count($pgPassBits)) { | |
| print("Invalid .pgpass format, aborting.\n"); | |
| exit(1); | |
| } | |
| $fileName = sprintf('%s-%s.sql', $pgPassBits[2], date('Y-m-d')); | |
| $filePath = sprintf('%s/%s', realpath(__DIR__), $fileName); | |
| if (file_exists($filePath)) { | |
| unlink($filePath); | |
| } | |
| $pgDumpArguments = array_map(function (string $table): string { | |
| return sprintf("--exclude-table-data '%s'", $table); | |
| }, $excludeTableDataTables); | |
| // Generate the database dump | |
| $pgDumpCommand = vsprintf("pg_dump --no-acl --no-owner --host='%s' --username='%s' --dbname='%s' --file='%s' %s", [ | |
| $pgPassBits[0], $pgPassBits[3], $pgPassBits[2], $filePath, implode(' ', $pgDumpArguments), | |
| ]); | |
| printf("[%s] Generating database backup file\n\n %s\n\n", date('Y-m-d H:i:s'), $pgDumpCommand); | |
| shell_exec(trim($pgDumpCommand)); | |
| // Gzip the database dump | |
| $gzipCommand = sprintf('gzip -f %s', $filePath); | |
| printf("[%s] Compressing database backup file\n\n %s\n\n", date('Y-m-d H:i:s'), $gzipCommand); | |
| shell_exec(trim($gzipCommand)); | |
| // Display command to download the database | |
| $filePath = sprintf('%s.gz', $filePath); | |
| $scpCommand = sprintf('scp %s@%s:%s . && gunzip %s', get_current_user(), gethostname(), $filePath, basename($filePath)); | |
| printf("[%s] Download database backup file\n\n %s\n\n", date('Y-m-h H:i:s'), $scpCommand); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment