There are two variants of 'COPY' statement, copy a database and copy a table. The Geek Stuff welcomes your tips and guest articles.In this article, we will discuss the 'COPY' functionality of PostgreSQL database which is one of the most important and often used features in practical world. This article was written by SathiyaMoorthy, developer of Enterprise Postgres Query Analyser, an efficient tool for parsing postgresql log to generate html report, which can be used for fine tuning the postgres settings, and sql queries. The following psql command installs the product table in the geek stuff database. Restore all the postgres databases $ su postgres The above dumps the local database, and extracts it at the given hostname. Backup a local postgres database and restore to remote server using single command: $ pg_dump dbname | psql -h hostname dbname Psql:mydb.sql:62: WARNING: no privileges were grantedĢ. Psql:mydb.sql:61: WARNING: no privileges were granted Psql:mydb.sql:60: WARNING: no privileges could be revoked Psql:mydb.sql:59: WARNING: no privileges could be revoked Psql:mydb.sql:34: ERROR: must be member of role "geekstuff" Psql:mydb.sql:13: ERROR: must be owner of schema public While restoring, there may be following errors and warning, which can be ignored. This restores the dumped database to the erp_devel database. Restore a postgres database $ psql -U erp -d erp_devel -f mydb.sql If there are same table names in different schema then use the –schema SCHEMANAME option. To backup a specific table, use the –table TABLENAME option in the pg_dump command. Backup a specific postgres table $ pg_dump -table products -U geekstuff article -f onlytable.sql Verify whether all the databases are backed up, $ grep "^connect" all.sql $ pg_dumpall > all.sql Verify the backup: You can backup all the databases using pg_dumpall command. Geeker | sathiya | UTF8 Backup all postgres databases using pg_dumpall: Login as postgres / psql user: $ su postgres List the databases: $ psql -l To backup all databases, list out all the available databases as shown below. Name: employee_details_pkey Type: CONSTRAINT Schema: public Owner: geekstuff Tablespace:ĪDD CONSTRAINT employee_details_pkey PRIMARY KEY (emp_id) 2. Data for Name: employee_details Type: TABLE DATA Schema: public Owner: geekstuffĬOPY employee_details (employee_name, emp_id, designation, comments) FROM stdin Name: employee_details Type: TABLE Schema: public Owner: geekstuff Tablespace:ĪLTER TABLE public.employee_details OWNER TO geekstuff Following is a partial output of mydb.sql showing the dump information of employee_details table. It prompts for password, after authentication mydb.sql got created with create table, alter table and copy commands for all the tables in the erp database. This example will backup erp database that belongs to user geekstuff, to the file mydb.sql $ pg_dump -U geekstuff erp -f mydb.sql In this article, let us review several practical examples on how to use pg_dump to backup and restore.įor the impatient, here is the quick snippet of how backup and restore postgres database using pg_dump and psql: Backup: $ pg_dump -U How To Backup Postgres Database 1. Using pg_dump, you can backup a local database and restore it on a remote database at the same time, using a single command. To restore these dumps psql command is enough. It creates a *.sql file with CREATE TABLE, ALTER TABLE, and COPY SQL statements of source database. Pg_dump is an effective tool to backup postgres database. This is a guest post written by SathiyaMoorthy
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |