• Home

Postgres App Mac Upgrade Pg_dump

 

This is a quick reference guide for how to edit settings via the terminal command line.

To download a pdf copy of each OS
Windows Cheatsheet
Mac OS Cheatsheet
  1. Postgres.app is a full-featured PostgreSQL installation packaged as a standard Mac app. It includes everything you need to get started: we’ve even included popular extensions like PostGIS for geo data and plv8 for JavaScript. Postgres.app has a beautiful user interface and a convenient menu bar item.
  2. Manually Start Postgres Database Server: $ pgctl -D /usr/local/var/postgres start. Configuring Postgres: $ psql postgres (You may need to use sudo psql postgres) This will open postgres shell.
  3. 私はpgdumpツールを使用してPostgreSQLデータベースをダンプしようとしています。 $ pgdump books books. Out どのように私はこのエラーを取得しています。 pgdump: server version: 9.2.1; pgdump version: 9.1.6 pgdump: aborting because of server version mismatch-ignore-versionオプションは償却されました。.
  4. Jan 13, 2018  Run initdb - I'm going to point the data directory (where postgres stores all our data) to /usr/local/var/postgres, I'm also going to set the encoding to utf8. These are sane settings for my use (local web app development) but your usage may require some different settings. Use initdb -help to see the options. $ initdb /usr/local/var/postgres.
Starting/Stopping the Server
Refer to: http://www.postgresql.org/docs/current/interactive/app-pg-ctl.html

Postgres.app for Mac OSX 5 Installing PostgreSQL on Windows 5 Install postgresql with brew on Mac 7. Backing up the filesystem instead of using pgdumpall and pgdump 19 Examples 19. Backing up one database 19 Restoring backups 19. Delete duplicate records from postgres table 60 Update query with join between two tables alternative since. All I have for homebrew in this folder is a redis file, and when I simply run 'brew upgrade postgresql' it says postgresql-9.2.4 already installed – user2184718 Dec 24 '13 at 15:38 Then skip that step and proceed to the others. Postgres.app is a simple, native macOS app that runs in the menubar without the need of an installer. Open the app, and you have a PostgreSQL server ready and awaiting new connections. Close the app, and the server shuts down. PostgreSQL packages are available for macOS from the Fink Project. Please see the Fink.

Mac OS X
  1. Open Terminal
  2. Type su - postgres
  3. Type pg_ctl start or pg_ctl stop or pg_ctl restart
  4. - or - you may need to enter the full pathname of postgresql bin's folder including the location of the data folder if the PATH environment variables are set incorrectly. As in:
    /Library/PostgreSQL/11/bin/pg_ctl start -D /Library/PostgreSQL/11/data
    /Library/PostgreSQL/11/bin/pg_ctl stop -D /Library/PostgreSQL/11/data
  5. (Optional) Leave the terminal window open to view stderr log messages as you execute queries against the server.
Windows
  1. Open Control Panel
  2. Open Administrative Tools
  3. Open Services
  4. Find the PostgreSQL Server service
  5. Start, Stop or Restart the service
Reloading Changes to the Configuration File
Refer to: http://www.postgresql.org/docs/current/interactive/app-pg-ctl.html - simply sends the postgres process a SIGHUP signal, causing it to reread its configuration files (postgresql.conf, pg_hba.conf, etc.). This allows changing of configuration-file options that do not require a complete restart to take effect.
Mac OS X
  1. Open Terminal
  2. Type su - postgres
  3. Type pg_ctl reload
Windows
  1. Open Command Prompt
  2. Go to C:Program FilesPostgreSQL11bin (Do cd. to get back to C: prompt then type cd 'Program Files' - enter - cd PostgreSQL - enter - cd 11 enter - cd bin - enter
  3. pg_ctl reload
Backing Up A Database
Uncompressed backups from this point forward will use the .sql extenstion, while compressed backups will use the .backup extension.

Mac OS X - Uncompressed
  1. Open the terminal
  2. Type su - postgres
  3. Type pg_dump [DatabaseName] > [Path] e.g For Database 'Demo' ->
    pg_dump Demo > /Users/Shared/MyBackups/Demo.sql
Mac OS X - Compressed
  1. Open Terminal
  2. Type su - postgres
  3. Type pg_dump -F c -v [DatabaseName] > [Path] e.g For Database 'Demo' ->
    pg_dump -F c -v Demo > /Users/Shared/MyBackups/Demo.backup
Windows - Uncompressed
  1. Open Command Prompt
  2. Go to C:Program FilesPostgreSQL11bin (Do cd. to get back to C: prompt then type cd 'Program Files' - enter - cd PostgreSQL - enter - cd 11 enter - cd bin - enter
  3. pg_dump -U postgres -o [DatabaseName] > [Path] e.g For Database 'Demo' ->
    pg_dump -U postgres -o Demo > /Users/Shared/MyBackups/Demo.sql
Windows - Compressed
  1. Open Command Prompt
  2. Go to C:Program FilesPostgreSQL11bin (Do cd. to get back to C: prompt then type cd 'Program Files' - enter - cd PostgreSQL - enter - cd 11 enter - cd bin - enter
  3. pg_dump -U postgres -F c -v [DatabaseName] > [Path] e.g For Database 'Demo' ->
    pg_dump -U postgres -F c -v Demo > /Users/Shared/MyBackups/Demo.backup

How To Dump Postgres Database

Windows - Compressed (via pgAdmin on a different machine then the server)
  1. Open Command Prompt
  2. Go to C:Program FilespgAdmin III1.12 (Do cd. to get back to C: prompt then type cd 'Program Files' - enter - cd pgAdmin III - enter - cd 1.12 enter
  3. pg_dump --host [IP address of server] --port 5432 --username 'postgres' --format=c --compress=9 --file 'C:BoxOffice[DatabaseBackupName].backup [DatabaseName] e.g For Database 'Demo' -> pg_dump --host 192.168.0.3 --port 5432 --username 'postgres' --format=c --compress=9 --file 'C:BoxOfficeDemo.backup' Demo
Restoring a Database
Mac OS X - Uncompressed
  1. Open Terminal
  2. Type su - postgres
  3. Drop the existing database (If it exists)
  4. Create a new database with the same name
  5. Type psql -v [DatabaseName] < [Path] e.g for Database 'Demo' ->
    psql -v Demo < /Users/Shared/MyBackups/Demo.sql
Mac OS X - Compressed
  1. Open Terminal
  2. Type su - postgres
  3. Drop the existing database (If it exists)
  4. Create a new database with the same name
  5. Type pg_restore -F c -v -U postgres -d [DatabaseName] [Path] e.g for Database 'Demo' ->
    pg_restore -F c -v -U postgres -d Demo /Users/Shared/MyBackups/Demo.backup
Windows - Uncompressed

Restore Pg Dump Pgsql


  1. Open Command Prompt
  2. Go to C:Program FilesPostgreSQL11bin (Do cd. to get back to C: prompt then type cd 'Program Files' - enter - cd PostgreSQL - enter - cd 11 enter - cd bin - enter
  3. Type psql -U postgres [DatabaseName] < [Path] e.g for Database 'Demo' ->
    psql -U postgres Demo < /Users/Shared/MyBackups/Demo.sql
Windows - Compressed
  1. Open Command Prompt
  2. Go to C:Program FilesPostgreSQL11bin (Do cd. to get back to C: prompt then type cd 'Program Files' - enter - cd PostgreSQL - enter - cd 11 enter - cd bin - enter
  3. Type pg_restore -F c -v -U postgres -d [DatabaseName] < [Path] e.g for Database 'Demo' ->
    pg_restore -F c -v -U postgres -d Demo < C:BoxOffice/BackupsDemo.backup

Postgres.app 2

Postgres.app 2.0 or later has an automatic update function.Just open the app, and select “Check For Updates…” from the “Postgres” menu.

If you want to convert a data directory to a new major version (eg. 9.5 to 9.6), see migrating data.

From Postgres.app 9.5.x.x or 9.6.x.x

You can upgrade to Postgres.app 2 just by replacing the app in your /Applications folder.

  1. Quit the old version of Postgres.app
  2. Download the new version of Postgres.app and double-click the downloaded .dmg
  3. Replace the old version in /Applications with the new version
  4. Double-click the new version.Postgres.app 2 will automatically detect the existing data directories if they are in the standard location.If you are using a different location, add them manually by opening the sidebar and clicking the plus button.

From earlier versions of Postgres.app

If you want to upgrade from earlier versions of Postgres.app, you will need to migrate your data.

Macos catalina bugs

Alternatively, you can make a custom version of Postgres.app 2 that supports the older server versions. See this page for details.