PostgreSQL notes: Difference between revisions

From myWiki
Created page with "Linux - change to postgres user in command line interface # sudo su - postgres Backup database # pg_dump db_name > file_name Restore pg_dump file # psql db_name < f..."
 
No edit summary
Line 31: Line 31:


remote backup
remote backup
   pg_dump -U tecmint -h 10.10.20.10 -p 5432 tecmintdb > tecmintdb.sql
   pg_dump -U tecmint -h 192.168.1.9 -p 5432 cipher > 20220224_cipher.sql
 
remote restore
  psql -U stevet -h 192.168.1.9 -p 5432 cipher < 20220224_cipher.sql
 


Changing user privileges
Changing user privileges
   ALTER USER user_name WITH SUPERUSER/NOSUPERUSER;
   ALTER USER user_name WITH SUPERUSER/NOSUPERUSER;

Revision as of 12:41, 25 February 2022

Linux - change to postgres user in command line interface

  # sudo su - postgres

Backup database

  # pg_dump db_name > file_name

Restore pg_dump file

  # psql db_name < file_name

Some table modification commands

  ALTER TABLE some_table_name RENAME "row.names" TO id;
  ALTER TABLE some_table_name ALTER COLUMN id TYPE integer USING id::integer;
  ALTER TABLE some_table_name ADD PRIMARY KEY (id);

list databases

  postgres # psql
  postgres=# \l

dump from one host to another

  pg_dump -h host1 dbname | psql -h host2 dbname

Reload with

  psql dbname < archive.sql

Create a database

  createdb dbname

Using a zip archive with backup and restore

  pg_dump dbname | gzip > filename.gz
  gunzip -c filename.gz | psql dbname

remote backup

  pg_dump -U tecmint -h 192.168.1.9 -p 5432 cipher > 20220224_cipher.sql

remote restore

  psql -U stevet -h 192.168.1.9 -p 5432 cipher < 20220224_cipher.sql


Changing user privileges

  ALTER USER user_name WITH SUPERUSER/NOSUPERUSER;