Import and Export MySQL and PostgreSQL


There are many 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 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 because 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:


If you don’t know your version of PostgreSQL you can check it using the following command:

psql -U homestead
Password: secret
select version();

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

# 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               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            md5
#host    replication     postgres        ::1/128                 md5
host    all             all                  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 connect and import your data using psql.

You will also need to add the following line if you are trying to connect to the database from the host machine:

host  all  all md5

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:


Then exit:


Laravel Forge

If you are coming from Laravel Forge, you may need to create a user named forge if you are using the defaults provided when importing. If not, you may need to create the user you provisioned on Forge.

To create the user run:

CREATE USER [username] WITH PASSWORD 'secret';

Then grant privileges to the new user.


Import MySQL

If you want to import MySQL:

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


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.

Some Errors

If you need to restart postgresql using service postgresql restart on Homestead for Laravel and get an AUTHENTICATING FOR request you can use the password vagrant. Vagrant’s default password is vagrant.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.