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:
/etc/postgresql/9.5/main/pg_hba.conf
/usr/share/postgresql/9.5/pg_hba.conf.sample
If you don’t know your version of PostgreSQL you can check it using the following command:
psql -U homestead
Password: secret
select version();
\q
In the main /ect
file location at the bottom of the pg_hba.conf
file 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 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 0.0.0.0/0 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:
CREATE DATABASE [database]; GRANT ALL PRIVILEGES ON DATABASE [database] TO [username];
Then exit:
\q
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.
GRANT ALL PRIVILEGES ON DATABASE [database] TO [username];
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.
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