Import and Export MySQL and PostgreSQL

There are a number of ways to export and import data from a database. You can use a Mac client like Sequal Pro for MySQL, for example. You can do the same for PostgreSQL, though I have not settled on a Mac client for PostgreSQL.

Mac clients aside, you need can import and export your database SQL from the command line or from a bash script on your server as well. I use the following commands to handle importing and exporting SQL on my servers and development machines.

Export PostgreSQL

If you want to export PostgreSQL:

pg_dump -U [username] [database] -f [filepath]

Export MySQL

If you want to export MySQL:

mysqldump -u [username] -p[root_password] [database] > [filepath]

Note that there is no space after -p in the MySQL command.

Import PostgreSQL

If you want to import PostgreSQL:

psql [database] < [filepath]

Peer Authentication Fix

Note that in PostgreSQL you might get an error: "psql: FATAL: Peer authentication failed for user". I get this a lot becasue I use Laravel Homestead. You may be able to fix this error by editing your pg_hba.conf file. To find this file on Ubuntu update your file index using sudo updatedb and then search for the file using locate.

locate pg_hba.conf

You should see a list of files. For example:

/etc/postgresql/9.5/main/pg_hba.conf
/usr/share/postgresql/9.5/pg_hba.conf.sample

In the main /ect file location at the bottom of the pg_hba.conf file you will see:

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5
host    all             all             10.0.2.2/32               md5

Change peer in this section to md5 and save your changes. Then restart postgresql using the command sudo service postgresql restart. You can now conect and import your data using psql.

On Homestead

Also, you might need to specify the login user. For example, if my user is homestead I need to run:

psql [database] < [filepath] -U homestead

Create a Table

In some cases you may not have an import table. You can create on with psql as well.

Enter postgres:

psql postgres -U homestead

Then run the SQL:

CREATE DATABASE [database]; GRANT ALL PRIVILEGES ON DATABASE [database] TO [username];

Then exit:

\q

Import MySQL

If you want to import MySQL:

mysql -u [username] -p[root_password] [database] < [filepath]

Documentation

You can read about the backup process for PostgreSQL on their website for more details.

For MySQL, the documentation is broken up into multiple pages. One for exporting or dumping your database and another for importing your exported database.

Show Comments