Skip to main content

PostgreSQL dump list file

The error first started with something like 'INVALID COMMAND \N ..'. This actually not the real error. Use psql command line switch --set ON_ERROR_STOP=1 to stop immediately on error so we can see the actual error.

When restoring my postgresql dump to the new hosting, got error that saying plpgsql language already exists. This probably because my hosting already setup plpgsql in all databases by default while dump consist a line to create the language extension. Googling around, I found out that pg_restore provide what they call list file that list out what kind of object should be restored. So the first step is to generate the list file out of your database dump:- 
pg_restore -l db.dump > db.list
db.dump is your database dump file and when specifying -l option, the output would be list of object to restore. We save that list in db.list file. Now we can open up db.list with any text editor and comment out the line that mention the creation of plpgsql. The list file should look something like:- 
3178; 1262 1525521 DATABASE - cc_live myname
6; 2615 1313721 SCHEMA - audit myname
7; 2615 1313722 SCHEMA - cct myname
3; 2615 2200 SCHEMA - public postgres
3179; 0 0 COMMENT - SCHEMA public postgres
3180; 0 0 ACL - public postgres
;1007; 2612 1313725 PROCEDURAL LANGUAGE - plpgsql myname
639; 1247 1313728 TYPE cct daily_sale myname
641; 1247 1313729 DOMAIN public bigint_unsigned myname
So we comment out (by putting semicolon at the beginning of the line) plpgsql object. Then we restoring the dump, we specify the list file to pg_restore:- 
pg_restore -x -O -L db.list db.dump | psql new_db
This is useful if the dump file very large or using the archive format which mean you can't edit it directly with editor. The list file supposed to be editable.
Labels:

Comments

Post a Comment

Popular posts from this blog

PHP with docker

A friend asking about a PHP library and I decided to test whether that library is working. But I don't have PHP environment setup (we're Python shop btw). But thanks to docker, that's easy these days. docker run -it --tty --rm --volume $PWD:/app --user $(id -u):$(id -g) composer require google/apiclient:^2.0 Then we just need to create the script to run, still in the same directory:- include_once __DIR__ . '/vendor/autoload.php'; $GCSE_API_KEY = "nqwkoigrhe893utnih_gibberish_q2ihrgu9qjnr"; $GCSE_SEARCH_ENGINE_ID = "937592689593725455:msi299dkne4de"; $client = new Google_Client(); $client->setApplicationName("My_App"); $client->setDeveloperKey($GCSE_API_KEY); $service = new Google_Service_Customsearch($client); $optParams = array("cx"=>self::GCSE_SEARCH_ENGINE_ID); $results = $service->cse->listCse("lol cats", $optParams); And we can run that script again using docker:- docker run -it --...
1 comment
Read more

The first step in learning new programming language

Is to prepare the basic environment where you can freely try and experiment with the new language features and tools. Maybe because I'm not programmer type person, but more as tinkerer/builder, I hate learning the language syntax and stuff. For years after I started "learning" Python, I can't barely write any Python code. But I have manage to try lot of Python cool apps because I have that environment for me to experiment with all Python based applications. All these cool apps that get me hooked to the language, not the syntax or whatever language features. And in my experiences, this is one reason why people failed to get hooked on the new language they want to learn. They started learning with some of the language syntax and eventually get bored, because not so much interesting stuff there. In whatever programming language, it's the ecosystem that made it lively, and where the real work happened. Early this year, I made it a point to learn Go programmin...
Post a Comment
Read more

AI coding tools fragmentation

Executive Summary The discussion centers on a tweet by DHH (David Heinemeier Hansson) from January 10, 2026 , arguing against the fragmentation of AI coding tools. DHH contends that developers do not want a separate CLI for every model provider (e.g., Anthropic’s Claude Code, OpenAI’s tools). Instead, he advocates for a unified interface —specifically citing OpenCode —that allows developers to swap models within a single environment. The thread reveals a three-way tension in the developer community between convenience (unified tools), corporate control (walled gardens), and sovereignty (running local models). Key Discussion Themes 1. The Fatigue of Fragmentation The Problem: Developers are exhausted by "model choice fatigue" and the need to manage multiple CLIs. As Rob Zolkos noted, the fragmentation is absurd, leading some to write scripts just to manage their AI CLIs. The Desire: There is a strong consensus (DHH, Will McGugan, Mustafa Ergisi) that a "universal s...
Post a Comment
Read more