Windows下用命令行/VBScript创建PostgreSQL 10数据库的问题解决

Hey there! Let's work through your PostgreSQL 10 problems on Windows one by one—first fixing that connection refused error, then getting the createdb command right, and even covering a VBScript option for creating databases.

1. Resolving the "Connection Refused" Error (pg_dump) #

You ran into this error when trying to use pg_dump:

pg_dump: [archiver (db)] connection to database "myDB" failed: could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432?
could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

This almost always means either the PostgreSQL service isn't running, or it's not configured to accept TCP/IP connections on localhost. Here's how to fix it:

Step 1: Start the PostgreSQL 10 Service #

• Press Win + R, type services.msc, and hit Enter to open the Services manager.
• Look for a service named postgresql-x64-10 (the exact name might vary slightly based on your installation).
• If it's not running, right-click it and select Start. To make it launch automatically on boot, right-click > Properties > set Startup type to Automatic.

Step 2: Configure PostgreSQL to Accept Local TCP/IP Connections #

• Navigate to your PostgreSQL 10 data directory—usually C:\Program Files\PostgreSQL\10\data.
• Open postgresql.conf with a text editor (run as administrator to save changes).
• Find the line starting with listen_addresses and update it to:

  listen_addresses = '127.0.0.1,::1'
  

  (This restricts connections to localhost only, which is safe for your use case.)
• Next, open pg_hba.conf in the same directory. Add these lines at the bottom to allow password-based local connections:

  host    all             all             127.0.0.1/32            md5
  host    all             all             ::1/128                 md5
  

• Save both files, then go back to the Services manager and restart the PostgreSQL 10 service.

2. Fixing the "Illegal Option" Error with createdb #

Your createdb command had incorrect syntax—createdb doesn't use the --dbname flag the way pg_dump does. Here's how to use it properly to create a new database:

First, the error you saw:

createdb: illegal option -- dbname=postgresql://postgres:123@localhost:5432/myDB
Try "createdb --help" for more information.

Correct createdb Commands #

Option 1: Basic Command (Interactive Password Prompt)

createdb -U postgres -p 5432 myNewDB

When you run this, it'll prompt you for the postgres user's password (enter 123 as you have it set).

Option 2: Using a Connection URI

If you prefer specifying credentials directly in the command (note: this exposes your password in command line history, so use with caution):

createdb "postgresql://postgres:123@localhost:5432/myNewDB"

Just pass the URI as a direct parameter—no need for --dbname.

VBScript Alternative to Create a Database #

If you want to use VBScript instead of the command line, save this as CreatePostgresDB.vbs and double-click to run it:

Set WshShell = CreateObject("WScript.Shell")
' Adjust the URI to match your username, password, port, and desired database name
dbCommand = "createdb ""postgresql://postgres:123@localhost:5432/myNewDB"""

' Execute the command and capture output
Set ExecProcess = WshShell.Exec(dbCommand)

' Print any output from the command
Do While ExecProcess.Status = 0
    WScript.Echo ExecProcess.StdOut.ReadLine()
Loop
' Print any error messages
WScript.Echo ExecProcess.StdErr.ReadAll()

Set WshShell = Nothing

3. Quick Note on Your Original pg_dump Command #

Once your PostgreSQL service is running and accessible, your original pg_dump command should work perfectly—just make sure the database myDB actually exists before running it:

pg_dump --dbname=postgresql://postgres:123@localhost:5432/myDB --format=p --clean

Remember, pg_dump is for backing up existing databases, not creating new ones—that's what createdb is for!

内容的提问来源于stack exchange，提问作者Codename K