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.confYou should see a list of files. For example:
/etc/postgresql/9.5/main/pg_hba.conf
/usr/share/postgresql/9.5/pg_hba.conf.sampleIf you don’t know your version of PostgreSQL you can check it using the following command:
psql -U homestead
Password: secretselect version();
\qIn 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 md5Change 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 md5On 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 homesteadCreate 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 homesteadThen run the SQL:
CREATE DATABASE [database]; GRANT ALL PRIVILEGES ON DATABASE [database] TO [username];Then exit:
\qLaravel 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.
