Open terminal and invoke this command:
psql -U postgres
to create new database, invoke this command:
CREATE DATABASE mydatabase;
to create new user, invoke this command:
CREATE USER dbuser WITH PASSWORD 'mypassword';
then, you can set your desired user-related preference like this
ALTER ROLE dbuser SET client_encoding TO 'utf8';
ALTER ROLE dbuser SET default_transaction_isolation TO 'read committed';
ALTER ROLE dbuser SET timezone TO 'UTC';
to grant privileges, invoke this command:
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO dbuser;
Quit from PostgreSQL console
sudo add-apt-repository ppa:jonathonf/python-3.6
sudo apt-get -y update
sudo apt-get install software-properties-common python-software-properties
sudo apt-get install -y python3.6
sudo apt-get install -y python3-pip
python3.6 -m pip install psycopg2-binary
CREATE TABLE “public”.”product”
product_id integer NOT NULL,
product_name character varying,
supplier_name character varying,
ALTER TABLE “public”.”product” ADD UNIQUE (“product_id”);
ALTER TABLE “public”.”product” ADD PRIMARY KEY (“product_id”);
to get list of columns on certain table use this command:
select column_name, data_type from INFORMATION_SCHEMA.COLUMNS where table_name = ‘table’;
for version >= 8.4.0:
pg_dump –column-inserts –data-only –table=<table> <database>
for version < 8.4.0:
pg_dump -D -t <table> <database>
We usually use DESCRIBE command in MySQL to display table structure, in PostgreSQL we use this command:
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ‘table’;
There are two tools to look at, depending on how you created the dump file.
Your first source of reference should be the man page
pg_dump(1) as that is what creates the dump itself. It says:
Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql(1). Script files can be used to reconstruct the database even on other machines and other architectures; with some modifications even on other SQL database products.
The alternative archive file formats must be used with pg_restore(1) to rebuild the database. They allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored. The archive file formats are designed to be portable across architectures.
So depends on the way it was dumped out. You can probably figure it out using the excellent
file(1)command – if it mentions ASCII text and/or SQL, it should be restored with
psql otherwise you should probably use
Restoring is pretty easy:
psql -U <username> -d <dbname> -1 -f <filename>.sql
pg_restore -U <username> -d <dbname> -1 -f <filename>.dump
Check out their respective manpages – there’s quite a few options that affect how the restore works. You may have to clean out your “live” databases or recreate them from template0 (as pointed out in a comment) before restoring, depending on how the dumps were generated.
OIDs basically give you a built-in, globally unique id for every row, contained in a system column (as opposed to a user-space column). That’s handy for tables where you don’t have a primary key, have duplicate rows, etc. For example, if you have a table with two rows identical rows, and you want to delete the oldest of the two, you could do that using the oid column.
In my experience, the feature is generally unused in most postgres-backed applications (probably in part because they’re non-standard), and their use is essentially deprecated:
In PostgreSQL 8.1 default_with_oids is off by default; in prior versions of PostgreSQL, it was on by default.
The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled. Applications that require OIDs for a particular table should specify WITH OIDS when creating the table. This variable can be enabled for compatibility with old applications that do not follow this behavior.